Re: sorting and grouping with min/max

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...

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°