Ivan Sergio Borgonovo <m...@webthatworks.it> writes:
>>> David Fetter <da...@fetter.org> wrote:
>>>> In 8.4, you'll be able to do:
>>>> WITH d AS (
>>>> SELECT DISTINCT c1, c2 FROM table1
>>>> )
>>>> SELECT count(*) FROM d;
>>> Nice, but what will be the difference from
>>> select count(*) from (select distinct c1, c2 from t);
>>> ?
>>> Optimisation?
>> None especially.
> So what would be the advantage compared to subselect?
None, David just has WITH on the brain ;-)
The subselect syntax certainly seems like the one most likely to work
across different SQL implementations. WITH is a pretty recent addition
to the SQL spec, and DISTINCT with multiple aggregate arguments isn't
in the spec at all. The COUNT(DISTINCT ROW(x,y)) hack is a cute idea
but I'm dubious that that's portable either (it certainly doesn't work
in pre-8.4 PG).
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
