pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: David Garamond <lists@zara.6.isreserved.com>
To: pgsql-sql@postgresql.org
Subject: olympics ranking query
Date: Fri, 20 Aug 2004 23:40:08 +0700
Message-ID: <41262968.2000507@zara.6.isreserved.com> (raw )
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
view thread (8+ messages) latest in thread
Message-ID: <41262968.2000507@zara.6.isreserved.com>
Permalink: ../41262968.2000507@zara.6.isreserved.com/
Also on: postgresql.org/message-id/41262968.2000507@zara.6.isreserved.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@zara.6.isreserved.com
Subject: Re: olympics ranking query
In-Reply-To: <41262968.2000507@zara.6.isreserved.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox