Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
От | Peter Eisentraut |
---|---|
Тема | Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace |
Дата | |
Msg-id | Pine.LNX.4.21.0001271922100.356-100000@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
On 2000-01-26, Tom Lane mentioned: > Peter Eisentraut <peter_e@gmx.net> writes: > > Our documents say that DISTINCT ON is equivalent to GROUP BY. I still > > don't see why that wouldn't be true. You can always rewrite > > select distinct on a a,b from test > > as > > select a, xxx(b) from test group by a > > where xxx is some aggregate function (presumably min or max). > > Not really. Look at Julian's example. He can't rewrite as > > select a, min(b), min(c) from test group by a > > because the idea is to get the c that corresponds to the min b. I see. I believe what you want is this: select one.a, two.b, two.c from (select a, min(b) as "min_b" from test group by a) as one, (select b, c from test) as two where one."min_b" = two.b Not sure if this is completely legal as it stands but at least the idea would be to join the grouped select with the plain one to get the c corresponding to the minimum b. But of course we don't offer that, so it's distinct on until then. (It would really surprise me if the distinct on functionality was not at all possible to emulate using SQL, since in my experience it is fairly complete with regards to querying options at least.) -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
В списке pgsql-hackers по дате отправления: