Skip site navigation (1) Skip section navigation (2)

Re: sorting and grouping with min/max

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: sorting and grouping with min/max
Date: 2009-03-02 06:14:25
Message-ID: 20090302061425.GA19849@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-novice
In response to Valentin Gjorgjioski :
> On 01.03.2009 14:08 Andreas Kretschmer wrote:
> >Valentin Gjorgjioski<tinodj(at)mt(dot)net(dot)mk>  wrote:
> >>>i'm looking for a query that returns one row for each player with the
> >>>smallest cmd_nr value. after several hours i figured out the following
> >>>query
> >>>
> >>>SELECT * FROM command_queue GROUP BY id, params, player, cmd_nr, date
> >>>HAVING (player, cmd_nr) IN (SELECT player, MIN(cmd_nr) FROM
> >>>command_queue GROUP BY player)
> >>well... for sure it would be simpler if you say
> >>
> >>SELECT * FROM command_queue where
> >>(player, cmd_nr) IN (SELECT player, MIN(cmd_nr) FROM command_queue GROUP
> >>BY player)
> >>
> >>But, can be even simpler? I hate subqueries...
> >
> >Maybe it is faster with an JOIN instead the IN(...), other solution:
> >wait for 8.4 windowing functions...
> 
> vivawasser wrote:
> Thanks for your solution, i dont like subqueries either but couldnt 
> think of another way to solve that problem within one rush.
> 
> @Andreas Kretschmer
> I have absolutly no clue how use a join on this query.

test=*# select * from foo;
 g | val
---+-----
 1 |   1
 1 |   5
 1 |   3
 2 |  10
 2 |   5
 2 |   1
 3 |   2
 3 |   8
(8 rows)

test=*# select f1.* from foo f1 inner join (
  select g, max(val) as val from foo group by g) f2 on ((f1.g, f1.val)=(f2.g, f2.val));
 g | val
---+-----
 1 |   5
 2 |  10
 3 |   8
(3 rows)


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

In response to

pgsql-novice by date

Next:From: Nico CallewaertDate: 2009-03-02 09:58:48
Subject: Function variable assignment question
Previous:From: Shabala DeshpandeDate: 2009-03-02 05:43:44
Subject: Re: Version details for psql/postmaster

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group