Re: sorting and grouping with min/max

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