Postgres Pro
Downloads
Home   >   mailing lists

Thread: sql group by statement

sql group by statement

From
"Albrecht Berger"
Date:
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

Re: sql group by statement

From
dima
Date:
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.

Re: sql group by statement

From
"Albrecht Berger"
Date:
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.
>
>
>

Re: sql group by statement

From
Manfred Koizar
Date:
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

Re: sql group by statement

From
Jean-Luc Lachance
Date:
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

Re: sql group by statement

From
Tom Lane
Date:
"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