Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx> writes: >>> David Fetter <david@xxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
