Re: select a ranking

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