X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4B6325E37CE for ; Fri, 20 Aug 2004 13:37:54 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 88436-01 for ; Fri, 20 Aug 2004 16:37:59 +0000 (GMT) Received: from 10.hostcorporate.com (unknown [69.41.231.186]) by svr1.postgresql.org (Postfix) with SMTP id F04585E3632 for ; Fri, 20 Aug 2004 13:37:47 -0300 (ADT) Received: (qmail 28059 invoked from network); 20 Aug 2004 16:29:11 -0000 Received: from localhost (HELO localhost.localdomain) (127.0.0.1) by localhost.10.hostcorporate.com with SMTP; 20 Aug 2004 16:29:11 -0000 Message-ID: <41262968.2000507@zara.6.isreserved.com> Date: Fri, 20 Aug 2004 23:40:08 +0700 From: David Garamond User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7b) Gecko/20040421 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: olympics ranking query Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/227 X-Sequence-Number: 18724 See http://www.athens2004.com/en/OlympicMedals/medals?noc=MGL . create table countrymedal ( countryid CHAR(3) PRIMARY KEY, gold INT NOT NULL, silver INT NOT NULL, bronze INT NOT NULL); COPY countrymedal (countryid, gold, silver, bronze) FROM stdin; ITA 5 6 3 FRA 5 3 5 UKR 5 1 1 RUS 4 8 10 GER 4 4 7 TUR 3 0 1 KOR 2 7 3 NED 2 5 4 HUN 2 3 1 SVK 2 2 1 ROM 2 0 2 GRE 2 0 1 POL 1 2 1 BLR 1 1 2 SUI 1 0 1 UAE 1 0 0 GBR 0 4 4 AUT 0 3 0 PRK 0 2 1 ESP 0 2 0 CUB 0 1 5 CZE 0 1 2 ZIM 0 1 1 USA 15 11 10 CHN 15 9 8 JPN 9 4 2 AUS 7 5 8 GEO 1 1 0 RSA 1 1 0 BUL 1 0 2 THA 1 0 2 IND 0 1 0 INA 0 1 0 KAZ 0 1 0 POR 0 1 0 SCG 0 1 0 AZE 0 0 2 BEL 0 0 2 BRA 0 0 2 DEN 0 0 2 ARG 0 0 1 CAN 0 0 1 COL 0 0 1 CRO 0 0 1 ISR 0 0 1 MGL 0 0 1 SLO 0 0 1 TRI 0 0 1 \. create sequence seq1; create sequence seq2; -- query #1: list of ranks by gold select setval('seq1', 1); select setval('seq2', 1); select setval('seq1', currval('seq1')+setval('seq2',count(*)))-count(*) as rank, count(*) as numranker, gold, silver, bronze from countrymedal group by gold, silver, bronze order by gold desc, silver desc, bronze desc; -- result of query #1 rank | numranker | gold | silver | bronze ------+-----------+------+--------+-------- 1 | 1 | 15 | 11 | 10 2 | 1 | 15 | 9 | 8 3 | 1 | 9 | 4 | 2 4 | 1 | 7 | 5 | 8 5 | 1 | 5 | 6 | 3 6 | 1 | 5 | 3 | 5 7 | 1 | 5 | 1 | 1 8 | 1 | 4 | 8 | 10 9 | 1 | 4 | 4 | 7 10 | 1 | 3 | 0 | 1 11 | 1 | 2 | 7 | 3 12 | 1 | 2 | 5 | 4 13 | 1 | 2 | 3 | 1 14 | 1 | 2 | 2 | 1 15 | 1 | 2 | 0 | 2 16 | 1 | 2 | 0 | 1 17 | 1 | 1 | 2 | 1 18 | 1 | 1 | 1 | 2 19 | 2 | 1 | 1 | 0 21 | 2 | 1 | 0 | 2 23 | 1 | 1 | 0 | 1 24 | 1 | 1 | 0 | 0 25 | 1 | 0 | 4 | 4 26 | 1 | 0 | 3 | 0 27 | 1 | 0 | 2 | 1 28 | 1 | 0 | 2 | 0 29 | 1 | 0 | 1 | 5 30 | 1 | 0 | 1 | 2 31 | 1 | 0 | 1 | 1 32 | 5 | 0 | 1 | 0 37 | 4 | 0 | 0 | 2 41 | 8 | 0 | 0 | 1 (32 rows) -- query #2: list of countries ordered by their ranks select setval('seq1', 1); select setval('seq2', 1); select (case numranker when 1 then '' else '=' end) || rank as rank, countryid, cm.gold, cm.silver, cm.bronze from countrymedal cm left join (select setval('seq1', currval('seq1')+setval('seq2',count(*)) )-count(*) as rank, count(*) as numranker, gold, silver, bronze from countrymedal group by gold, silver, bronze order by gold desc, silver desc, bronze desc ) t1 on cm.gold=t1.gold and cm.silver=t1.silver and cm.bronze=t1.bronze order by t1.rank; -- result of query #2 rank | countryid | gold | silver | bronze ------+-----------+------+--------+-------- 1 | USA | 15 | 11 | 10 2 | CHN | 15 | 9 | 8 3 | JPN | 9 | 4 | 2 4 | AUS | 7 | 5 | 8 5 | ITA | 5 | 6 | 3 6 | FRA | 5 | 3 | 5 7 | UKR | 5 | 1 | 1 8 | RUS | 4 | 8 | 10 9 | GER | 4 | 4 | 7 10 | TUR | 3 | 0 | 1 11 | KOR | 2 | 7 | 3 12 | NED | 2 | 5 | 4 13 | HUN | 2 | 3 | 1 14 | SVK | 2 | 2 | 1 15 | ROM | 2 | 0 | 2 16 | GRE | 2 | 0 | 1 17 | POL | 1 | 2 | 1 18 | BLR | 1 | 1 | 2 =19 | GEO | 1 | 1 | 0 =19 | RSA | 1 | 1 | 0 =21 | BUL | 1 | 0 | 2 =21 | THA | 1 | 0 | 2 23 | SUI | 1 | 0 | 1 24 | UAE | 1 | 0 | 0 25 | GBR | 0 | 4 | 4 26 | AUT | 0 | 3 | 0 27 | PRK | 0 | 2 | 1 28 | ESP | 0 | 2 | 0 29 | CUB | 0 | 1 | 5 30 | CZE | 0 | 1 | 2 31 | ZIM | 0 | 1 | 1 =32 | IND | 0 | 1 | 0 =32 | INA | 0 | 1 | 0 =32 | KAZ | 0 | 1 | 0 =32 | POR | 0 | 1 | 0 =32 | SCG | 0 | 1 | 0 =37 | AZE | 0 | 0 | 2 =37 | BEL | 0 | 0 | 2 =37 | BRA | 0 | 0 | 2 =37 | DEN | 0 | 0 | 2 =41 | ARG | 0 | 0 | 1 =41 | CAN | 0 | 0 | 1 =41 | COL | 0 | 0 | 1 =41 | CRO | 0 | 0 | 1 =41 | ISR | 0 | 0 | 1 =41 | MGL | 0 | 0 | 1 =41 | SLO | 0 | 0 | 1 =41 | TRI | 0 | 0 | 1 Challenge question: is there a simpler way to do query #1 (without any PL, and if possible without sequences too? -- dave