Re: sorting and grouping with min/max

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.

SELECT cq.*, min(cmd_nr) FROM command_queue AS cq join command_queue AS
cq1 on cq.player=cq1.player
GROUP BY cq.id, cq.params,cq.player,cq.cmd_nr,cq.date

(I didn't try sql in query editor, i just wrote it here, it can contains
small errors)

Because table is not normalized, this join looks bit strange :)

>
>
> Andreas
>
>
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.0.237 / Virus Database: 270.11.5/1978 - Release Date: 03/01/09 07:04:00
>

--
Valentin Gjorgjioski
Mobile: +386 40 1 26 26 7
Skype: tinodj
Email: gjorgjioski(at)gmail(dot)com
Web: http://kt.ijs.si/ValentinGjorgjioski/
--
Human knowledge belongs to the world
--