Re: sorting and grouping with min/max

In response to Valentin Gjorgjioski :
> On 01.03.2009 14:08 Andreas Kretschmer wrote:
> >Valentin Gjorgjioski<tinodj@mt.net.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