Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

To rank records

1 view
Skip to first unread message

Ng K C Paul

unread,
Mar 9, 1999, 3:00:00 AM3/9/99
to
How to rank a table of score records?

Ng K C Paul

unread,
Mar 9, 1999, 3:00:00 AM3/9/99
to
yes,

score rank
----- ----
7.8 3
0.9 10
3.8 5
5.2 4
24.3 2
1.2 9
0.5 12
0.3 13
0.8 11
1.4 8
2.7 7
27.9 1
2.8 6

Anthony Mandic (no.sp_am@_.agd.nsw.gov.au) wrote:
: Ng K C Paul wrote:
: >
: > How to rank a table of score records?
:
: By score?
:
: -am

Geoff Winkless

unread,
Mar 9, 1999, 3:00:00 AM3/9/99
to
Ng K C Paul wrote:
> score rank
> ----- ----
> 7.8 3
> 0.9 10
> 3.8 5
> 5.2 4
> 24.3 2
> 1.2 9
> 0.5 12
> 0.3 13
> 0.8 11
> 1.4 8
> 2.7 7
> 27.9 1
> 2.8 6

Am I being obtuse here... what's wrong with just ORDERing the output by
descending score?

Geoff

Ben Curthoys

unread,
Mar 9, 1999, 3:00:00 AM3/9/99
to
i think he wants the rank to be created too.

how about creating a temp. table with an identity field, INSERTing INTO it
order by score, using the identity field as the rank, and then deleting the
temp table.


>> score rank
>> ----- ----
>> 7.8 3
>> 0.9 10


...

George Lamptey

unread,
Mar 9, 1999, 3:00:00 AM3/9/99
to
Here are some variations for Ranking results: eg Ranking total_sales by title_id and accomodating tied results

Example (1)

--  b.  Variation to include rows that tie for the rank specified

CREATE PROC rank_sales1 (@limit int) AS
  SELECT x.title_id, x.total_sales, rank=count(*)
    FROM titles x, titles y
    WHERE y.total_sales >= x.total_sales
    GROUP BY x.title_id, x.total_sales
    HAVING count(*) -
           sum(1-abs(sign(x.total_sales-y.total_sales)))
             <= @limit
    ORDER BY count(*)

exec rank_sales1 6

Result (1)

title_id total_sales         rank
-------- ----------- -----------
MC3021         22246           1
BU2075         18722            2
TC4203         15096            3
PC1035          8780             4
BU1032          4095             8
BU7832          4095             8
PC8888          4095             8
TC7777          4095             8

Example(2)

CREATE PROC rank_sales1 (@limit int) AS
  SELECT x.title_id, x.total_sales,
         rank=count(distinct y.total_sales)
    FROM titles x, titles y
    WHERE y.total_sales >= x.total_sales
    GROUP BY x.title_id, x.total_sales
    HAVING count(distinct y.total_sales) <= @limit
    ORDER BY rank

exec rank_sales2 6

Results(2)

title_id total_sales     rank
-------- ----------- -----------
MC3021         22246           1
BU2075         18722           2
TC4203         15096           3
PC1035          8780           4
BU1032          4095           5
BU7832          4095           5
PC8888          4095           5
TC7777          4095           5
PS3333          4072           6

I hope this helps:. You can run these procs against the sample pubs2 database that comes with ASE.
Note fns

* sign(+positive value) = 1
   sign (-negative value)= -1
   sign(0) = 0

abs (+/- value) = +positive value
abs(0) = 0

George Lamptey
Consultant
Sybase Professional Services (UK)

0 new messages