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

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