Re: sorting and grouping with min/max

On 01.03.2009 01:07 vivawasser wrote:
> Hi everybody,
>
> my table is:
> id params player cmd_nr date
> 1 a:4:{s:3:"cmd";s:7:"bewegen";s:2:"id";s:1:"8";s:8... dompie 1
> 2009-02-28 23:45:48.020761+01
> 2 a:4:{s:3:"cmd";s:7:"bewegen";s:2:"id";s:1:"8";s:8... dompie 2
> 2009-02-28 23:45:48.530177+01
> 3 a:4:{s:3:"cmd";s:7:"bewegen";s:2:"id";s:1:"8";s:8... dompie 3
> 2009-02-28 23:45:48.977044+01
> 5 a:4:{s:3:"cmd";s:3:"neu";s:4:"name";s:4:"Oger";s:... thoto 2
> 2009-02-28 23:46:20.754546+01
> 6 a:4:{s:3:"cmd";s:3:"neu";s:4:"name";s:6:"Magier";... thoto 3
> 2009-02-28 23:46:29.898683+01
> 7 a:4:{s:3:"cmd";s:3:"neu";s:4:"name";s:5:"Prinz";s... thoto 4
> 2009-02-28 23:46:37.643187+01
> 8 a:4:{s:3:"cmd";s:3:"neu";s:4:"name";s:10:"Prinzes... tamborin 1
> 2009-02-28 23:46:51.675636+01
> 9 a:4:{s:3:"cmd";s:3:"neu";s:4:"name";s:6:"Drache";... tamborin 2
> 2009-02-28 23:47:23.30321+01
>
> 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...