olympics ranking query

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