Обсуждение: select a ranking

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;

On Thu, Jul 18, 2002 at 03:29:10PM +0200, Janning Vygen wrote:
> 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)

[snip]

> so heres is my question:
> is there an easier way to create a ranking?

Yep, in the presentation tool. Pseudo-code is:

For each row in result
  print rownumber
  print contents of row

Why do you want to do this in SQL when it's clearly not the right place for
it?

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Am Freitag, 19. Juli 2002 02:41 schrieb Martijn van Oosterhout:
> On Thu, Jul 18, 2002 at 03:29:10PM +0200, Janning Vygen wrote:
>
> > i do some ordering on a table and need a ranking by points from a
> > table with names and points
>
> > is there an easier way to create a ranking?
>
> Yep, in the presentation tool. Pseudo-code is:
>
> For each row in result
>   print rownumber
>   print contents of row

this wont work because the ranking is not equal to the rownumber.
there are very often two second places. and there might be a
complicated logic to the ordering. there might be many columns
affected to to the ordering in sql.

the presentation tool can just number the rows, but it cant decide
easily if there are two second places. in sport rankings you have
often more than five ordering parameters and you still can have two
second places. So the presentation tool needs to know this ordering
logic to decide how to do the ranking. if there are changes in
ranking/ordering logic, i need to update all my presentation
clients...

> Why do you want to do this in SQL when it's clearly not the right
> place for it?

but you do the ordering in sql anyway. it would be just fine to add a
column with the ranking.

kind regards,
janning

On Thu, 18 Jul 2002 15:29:10 +0200, Janning Vygen <vygen@gmx.de>
wrote:
>i ve thought of it and made the conclusion the easiest way is with
>sequences (see below: working code, tested). it works fine,

... only as long as there are no more than two persons with equal
score.  Try adding a third person with 13 points:
 ranking |  name   | points
---------+---------+-------
       1 | jim     |     10
       2 | john    |     13
       2 | bob     |     13
       3 | janning |     13
       5 | carl    |     15
(5 rows)

>so heres is my question:
>is there an easier way to create a ranking?

Don't know if this qualifies as easier :-), at least it is pure SQL:

SELECT (SELECT COUNT(*)
          FROM person p2
         WHERE p2.points < p1.points) + 1 AS ranking,
       name, points
  FROM person p1
 ORDER BY 1;

But be aware, that this is going to take ages ...

Servus
 Manfred

On Fri, Jul 19, 2002 at 11:54:48 +0200,
  Janning Vygen <vygen@gmx.de> wrote:
>
> this wont work because the ranking is not equal to the rownumber.
> there are very often two second places. and there might be a
> complicated logic to the ordering. there might be many columns
> affected to to the ordering in sql.
>
> the presentation tool can just number the rows, but it cant decide
> easily if there are two second places. in sport rankings you have
> often more than five ordering parameters and you still can have two
> second places. So the presentation tool needs to know this ordering
> logic to decide how to do the ranking. if there are changes in
> ranking/ordering logic, i need to update all my presentation
> clients...

I have a site where people are ranked in how well they play various
boardgames. Their ranking is one more than the number of people
who have higher ratings than them. While you could generate this
number using a subselect, it wouldn't be very efficient. It is
very easy to do in the presentation script using a counter, a saved rating
and a saved count.