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: (view raw, whole thread or download thread mbox)
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.params,cq.player,cq.cmd_nr,

(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 -
> 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
Human knowledge belongs to the world

In response to


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-2017 The PostgreSQL Global Development Group