Sybase Database - is there a way to do a "multi-layered" RANK()?

Author

is there a way to do a "multi-layered" RANK()?

spkelly@sbcglobal.net

2006-04-04, 8:23 pm

Take the RANK example from Sybase...

SELECT emp_lname, salary, state,
ROW_NUMBER () OVER (ORDER BY salary DESC) "Rank"
FROM employee WHERE state IN ('NY','UT')

RESULTS:
Emp_Lname salary state Rank
Shishov 72995 UT 1
Wang 68400 UT 2
Cobb 62000 UT 3
Morris 61300 UT 4
Davidson 57090 NY 5
Martel 55700 NY 6
Blaikie 54900 NY 7
Diaz 54900 NY 7
Driscoll 48023 UT 9
Hildebrand 45829 UT 10
.... ... ... ...
Lynch 24903 UT 19

What I need is something like this:

RESULTS:
Shishov 72995 UT 1
Wang 68400 UT 2
Cobb 62000 UT 3
Morris 61300 UT 4
Driscoll 48023 UT 5
Hildebrand 45829 UT 6
.... ... ... ...
Lynch 24903 UT 10

Davidson 57090 NY 1
Martel 55700 NY 2
Blaikie 54900 NY 3
Diaz 54900 NY 3
.... ... ... ...
Smith 24053 NY 9

The only way i can see is to use a cursor and run a rank for each
state, individually...
I don't want to use a cursor... but items such as RANK() and SELECT TOP
n require that all the items be organized together... no "grouping"...

Thanks in advance for any help...

S. Kelly

Santa Cruz, CA

sundar

2006-04-07, 1:23 pm

Please try like this. As I do not have sybase i am unable to test but it
should work or similar query should work

select emp_lname, salary, state,

rank=
case
when state="NY" then select count(*) from emp b
where a.salary <=b.salary and state="NY"
group by a.salary
when state="UT" then select count(*) from emp c
where a.salary <=c.salary and state="UT"
group by a.salary

from employee a
where state in ("NY","UT")

order by a.state,rank

thanks,
V.Sundar

spkelly@sbcglobal.net wrote:
>Take the RANK example from Sybase...
>
>SELECT emp_lname, salary, state,
>ROW_NUMBER () OVER (ORDER BY salary DESC) "Rank"
>FROM employee WHERE state IN ('NY','UT')
>
>RESULTS:
>Emp_Lname salary state Rank
>Shishov 72995 UT 1
>Wang 68400 UT 2
>Cobb 62000 UT 3
>Morris 61300 UT 4
>Davidson 57090 NY 5
>Martel 55700 NY 6
>Blaikie 54900 NY 7
>Diaz 54900 NY 7
>Driscoll 48023 UT 9
>Hildebrand 45829 UT 10
>... ... ... ...
>Lynch 24903 UT 19
>
>What I need is something like this:
>
>RESULTS:
>Shishov 72995 UT 1
>Wang 68400 UT 2
>Cobb 62000 UT 3
>Morris 61300 UT 4
>Driscoll 48023 UT 5
>Hildebrand 45829 UT 6
>... ... ... ...
>Lynch 24903 UT 10
>
>Davidson 57090 NY 1
>Martel 55700 NY 2
>Blaikie 54900 NY 3
>Diaz 54900 NY 3
>... ... ... ...
>Smith 24053 NY 9
>
>The only way i can see is to use a cursor and run a rank for each
>state, individually...
>I don't want to use a cursor... but items such as RANK() and SELECT TOP
>n require that all the items be organized together... no "grouping"...
>
>Thanks in advance for any help...
>
>S. Kelly
>
>Santa Cruz, CA

--CELKO--

2006-04-08, 7:23 am

Look at the PARTITION option in the teh OVER() clause.

Sponsored Links