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
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
