Thread: sorting and grouping with min/max
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)
that seems to me a bit complex for such a simple task. Is there maybe a
more easy way to achieve the same result?
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...
Valentin Gjorgjioski <tinodj@mt.net.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°
On 01.03.2009 14:08 Andreas Kretschmer wrote: > Valentin Gjorgjioski<tinodj@mt.net.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@gmail.com Web: http://kt.ijs.si/ValentinGjorgjioski/ -- Human knowledge belongs to the world --
In response to Valentin Gjorgjioski : > On 01.03.2009 14:08 Andreas Kretschmer wrote: > >Valentin Gjorgjioski<tinodj@mt.net.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. test=*# select * from foo; g | val ---+----- 1 | 1 1 | 5 1 | 3 2 | 10 2 | 5 2 | 1 3 | 2 3 | 8 (8 rows) test=*# select f1.* from foo f1 inner join ( select g, max(val) as val from foo group by g) f2 on ((f1.g, f1.val)=(f2.g, f2.val)); g | val ---+----- 1 | 5 2 | 10 3 | 8 (3 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net