Re: getting the ranks of items

-----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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200505022047
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCdssgvJuQZxSWSsgRAnbMAKCZyehHPTarYGB7YqkYFOrafOF1KwCg8V7E
3fveOsUWj2AgWtmQdR7S/uU=
=KcOL
-----END PGP SIGNATURE-----