Hi,
i do some ordering on a table and need a ranking by points from a
table with names and points (see sample code at the end of the mail)
i would like to get a table with numbers in front of it
like
1. jim 13
2. bob 15
2. john 15
4. peter 20
i ve thought of it and made the conclusion the easiest way is with
sequences (see below: working code, tested). it works fine, but seems
to be very complicated because i do an ordering in the first place
anyway, so it would be easy to add ranking in the client application.
and sequences are not sql standard, aren't they?
so heres is my question:
is there an easier way to create a ranking?
kind regards,
janning
only code below this line
-----------------------
-- startup
CREATE TABLE person (name text, points int4);
INSERT INTO person VALUES ('jim', 10);
INSERT INTO person VALUES ('john', 13);
INSERT INTO person VALUES ('bob', 13);
INSERT INTO person VALUES ('carl', 15);
-- get ranking
CREATE SEQUENCE ranking;
CREATE TEMP TABLE rank1 AS
SELECT nextval('ranking') AS rank, *
FROM (
SELECT name, points
FROM person
ORDER BY points
) AS rank;
SELECT CASE WHEN COALESCE(r2.points, -1) = r1.points
THEN r2.rank
ELSE r1.rank
END AS ranking,
r1.name,
r1.points
FROM rank1 AS r1
LEFT JOIN rank1 AS r2
ON (r2.rank = r1.rank - 1);
-- garbage collection
DROP SEQUENCE ranking;
DROP TABLE rank1;
DROP TABLE person;
