Обсуждение: sql group by statement
Hello, I have a problem, which I'm not able to solve with a simple query : I need a resultset with distinct id's, but the max val2 of each id. I tried to group by id, but though I need the pk in my resultset I have to group it too, which "destroys" the group of val2. Can this be done without a huge query ? Table : pk id val1 val21 1 2 32 1 2 43 2 1 14 1 0 55 2 1 8 Needed Result : pk id val1 val24 1 0 55 2 1 8 Thx berger
see the yesterday's thread about DISTINCT ON (non-standard Postgres feature) > I have a problem, which I'm not able to solve with a simple query : > > I need a resultset with distinct id's, but the max val2 of each id. > I tried to group by id, but though I need the pk in my resultset > I have to group it too, which "destroys" the group of val2.
but how do I know that "distinct on" doesn't cut off the row with max(val2) of that id that I need ? > see the yesterday's thread about DISTINCT ON (non-standard Postgres feature) > > > I have a problem, which I'm not able to solve with a simple query : > > > > I need a resultset with distinct id's, but the max val2 of each id. > > I tried to group by id, but though I need the pk in my resultset > > I have to group it too, which "destroys" the group of val2. > > >
On Fri, 13 Sep 2002 12:29:21 +0200, "Albrecht Berger" <berger1517@gmx.ch> wrote: >Table : >pk id val1 val2 > 1 1 2 3 > 2 1 2 4 > 3 2 1 1 > 4 1 0 5 > 5 2 1 8 > > >Needed Result : >pk id val1 val2 > 4 1 0 5 > 5 2 1 8 Albrecht, "DISTINCT ON eliminates rows that match on all the specified expressions, keeping only the first row of each set of duplicates." So the trick is to sort appropriately: SELECT DISTINCT on (id) pk, id, val1, val2 FROM yourtable ORDER BY id asc, val2 desc, pk desc; ServusManfred
How about: select * from <Table> where (id, val2) in ( select id, max(val2) from <Table> group by id); JLL Albrecht Berger wrote: > > Hello, > I have a problem, which I'm not able to solve with a simple query : > > I need a resultset with distinct id's, but the max val2 of each id. > I tried to group by id, but though I need the pk in my resultset > I have to group it too, which "destroys" the group of val2. > > Can this be done without a huge query ? > > Table : > pk id val1 val2 > 1 1 2 3 > 2 1 2 4 > 3 2 1 1 > 4 1 0 5 > 5 2 1 8 > > > Needed Result : > pk id val1 val2 > 4 1 0 5 > 5 2 1 8 > > > Thx > berger > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
"Albrecht Berger" <berger1517@gmx.ch> writes:
> but how do I know that "distinct on" doesn't cut off
> the row with max(val2) of that id that I need ?
Because you do
SELECT DISTINCT ON (id) ... ORDER BY id, val2 DESC;
The DISTINCT keeps the first of each group of rows with the same id,
and by virtue of the ORDER BY (which acts first) the max val2 will be
the first row in that group. Note there's no GROUP BY in this approach.
regards, tom lane