pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsql group by statement
6+ messages / 5 participants
[nested] [flat]
* sql group by statement
@ 2002-09-13 10:29 Albrecht Berger <berger1517@gmx.ch>
0 siblings, 3 replies; 6+ messages in thread
From: Albrecht Berger @ 2002-09-13 10:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sql group by statement
@ 2002-09-13 10:33 dima <_pppp@mail.ru>
parent: Albrecht Berger <berger1517@gmx.ch>
2 siblings, 1 reply; 6+ messages in thread
From: dima @ 2002-09-13 10:33 UTC (permalink / raw)
To: Albrecht Berger <berger1517@gmx.ch>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sql group by statement
@ 2002-09-13 10:39 Albrecht Berger <berger1517@gmx.ch>
parent: dima <_pppp@mail.ru>
0 siblings, 1 reply; 6+ messages in thread
From: Albrecht Berger @ 2002-09-13 10:39 UTC (permalink / raw)
To: pgsql-sql
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.
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sql group by statement
@ 2002-09-13 12:16 Manfred Koizar <mkoi-pg@aon.at>
parent: Albrecht Berger <berger1517@gmx.ch>
2 siblings, 0 replies; 6+ messages in thread
From: Manfred Koizar @ 2002-09-13 12:16 UTC (permalink / raw)
To: Albrecht Berger <berger1517@gmx.ch>; +Cc: pgsql-sql
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;
Servus
Manfred
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sql group by statement
@ 2002-09-13 15:39 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Albrecht Berger <berger1517@gmx.ch>
2 siblings, 0 replies; 6+ messages in thread
From: Jean-Luc Lachance @ 2002-09-13 15:39 UTC (permalink / raw)
To: Albrecht Berger <berger1517@gmx.ch>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sql group by statement
@ 2002-09-14 15:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Albrecht Berger <berger1517@gmx.ch>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2002-09-14 15:03 UTC (permalink / raw)
To: Albrecht Berger <berger1517@gmx.ch>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-09-14 15:03 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-13 10:29 sql group by statement Albrecht Berger <berger1517@gmx.ch>
2002-09-13 10:33 ` dima <_pppp@mail.ru>
2002-09-13 10:39 ` Albrecht Berger <berger1517@gmx.ch>
2002-09-14 15:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-13 12:16 ` Manfred Koizar <mkoi-pg@aon.at>
2002-09-13 15:39 ` Jean-Luc Lachance <jllachan@nsd.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox