Ranking Results

[Damien]

Is it possible to rank grouped data like you can in Excel using the RANK
function?

Basically I have a table structured as below, and I'd like to be able to run
an update query on the rank column, to rank the policies in order of
due_date. I always know which date is rank 1.

from this:

case due_date rank
10 01/01/2000 1
10 01/01/2001
10 01/01/2003
20 01/01/2000 1
20 01/01/2001
20 01/01/2003

to this:

case due_date rank
10 01/01/2000 1
10 01/01/2001 2
10 01/01/2003 3
20 01/01/2000 1
20 01/01/2001 2
20 01/01/2003 3


Thanks


Damien

 

[Marshall Barton]

Is it possible to rank grouped data like you can in Excel using the RANK
function?

Basically I have a table structured as below, and I'd like to be able to run
an update query on the rank column, to rank the policies in order of
due_date. I always know which date is rank 1.

from this:

case due_date rank
10 01/01/2000 1
10 01/01/2001
10 01/01/2003
20 01/01/2000 1
20 01/01/2001
20 01/01/2003

to this:

case due_date rank
10 01/01/2000 1
10 01/01/2001 2
10 01/01/2003 3
20 01/01/2000 1
20 01/01/2001 2
20 01/01/2003 3

Generally, you would use a subquery to calculate the rank:

SELECT case, duedate,
(SELECT Count(*)
FROM table AS X
WHERE X.case = table.case
AND X.duedate <= table.duedate
) AS rank
FROM table

 

[Damien]

Thanks Marsh,

nice trick. Do you know of any articles or websites with similar subquery
magic on? That would be really useful.

Thanks


Damien

 

[Marshall Barton]

Thanks Marsh,

nice trick. Do you know of any articles or websites with similar subquery
magic on? That would be really useful.

I'm kind of a trial and error kind of guy so I'm not big on
that book learning stuff ;-)

I'm pretty sure that there's an SQL for Dummies, along with
a myriad of other SQL books, out there, but I have not used
any of them.