-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Well, if you don't need the ranks to be sequential, merely ordered:
CREATE TABLE ranker (id INT, age INT);
...
SELECT b.w-a.age AS rank, a.id, a.age
FROM (SELECT * FROM ranker ORDER BY age DESC, id) AS a,
(SELECT max(age)+1 AS w FROM ranker) as b;
rank | id | age
- ------+----+-----
1 | 5 | 22
3 | 2 | 20
3 | 3 | 20
3 | 8 | 20
7 | 4 | 16
7 | 7 | 16
11 | 6 | 12
13 | 1 | 10
However, if you *do* need them to be sequential:
SELECT setval('rank1', 1);
SELECT setval('rank2', 1);
SELECT setval('rank_seq', 1, false);
SELECT CASE WHEN a.age = b.age THEN currval('rank_seq') ELSE nextval('rank_seq') END AS rank,
a.id, a.age
FROM
(
SELECT nextval('rank1') AS ct, a.* FROM (SELECT id,age FROM ranker ORDER BY age DESC, id) AS a
UNION ALL
SELECT nextval('rank1') AS ct,null,null
) AS a
,
(
SELECT nextval('rank2') AS ct,null AS id,null AS age
UNION ALL
SELECT nextval('rank2') AS ct, b.* FROM (SELECT id,age FROM ranker ORDER BY age DESC, id) AS b
) AS b
WHERE a.ct = b.ct AND a.age IS NOT NULL
;
rank | id | age
- ------+----+-----
1 | 5 | 22
2 | 2 | 20
2 | 3 | 20
2 | 8 | 20
3 | 4 | 16
3 | 7 | 16
4 | 6 | 12
5 | 1 | 10
Neither of which are terribly efficient, but that wasn't a prerequisite :)
- --
Greg Sabino Mullane greg@xxxxxxxxxxxx
PGP Key: 0x14964AC8 200505022047
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFCdssgvJuQZxSWSsgRAnbMAKCZyehHPTarYGB7YqkYFOrafOF1KwCg8V7E
3fveOsUWj2AgWtmQdR7S/uU=
=KcOL
-----END PGP SIGNATURE-----
---------------------------(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
