Skip site navigation (1) Skip section navigation (2)

Re: sorting and grouping with min/max

From: Valentin Gjorgjioski <tinodj(at)mt(dot)net(dot)mk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: sorting and grouping with min/max
Date: 2009-03-01 16:26:43
Message-ID: 49AAB743.9000303@mt.net.mk (view raw or flat)
Thread:
Lists: pgsql-novice
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
--

In response to

Responses

pgsql-novice by date

Next:From: Shabala DeshpandeDate: 2009-03-02 05:43:44
Subject: Re: Version details for psql/postmaster
Previous:From: Andreas KretschmerDate: 2009-03-01 13:08:49
Subject: Re: sorting and grouping with min/max

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group