Applying a ranking - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Applying a ranking

Author

Topic

lee_h
Starting Member

36 Posts

Posted - 2003-06-25 : 03:30:07

from the following query:

select left(svt_title,20) title, pc_desc, sales, budget,
(select count(*)
from vw b
where 1=1
and a.pc_desc = b.pc_desc
and a.sales < b.sales
)+1 as rank
from vw a
order by pc_desc, sales desc, budget desc

I get the following results:

svt_title pc_desc sales budget rank
---------- --------------- ----------- ----------- -----------
Bullo: The Local Adult RH 611142 294177 1
Incredible Local Adult RH 396012 275216 2
Dopeland Local Adult RH 156034 165406 3
Territory Local Adult RH 147955 183631 4
The Hamilt Local Adult RH 87008 110270 5
Good, The Local Adult RH 80830 48490 6
Shoot Stra Local Adult RH 66874 59148 7
Swift As D Local Adult RH 66129 44071 8
Fed Up Wit Local Adult RH 52210 55117 9
Why First Local Adult RH 42949 44071 10
Floaty Boy Local Adult RH 36707 33799 11
Swan Bay Local Adult RH 32447 29381 12
True Grit Local Adult RH 22469 24245 13
The Flambo Local Adult RH 17623 25349 14
Over The E Local Adult RH 17272 33799 15
A Pasty-Fa Local Adult RH 0 588355 16
Walking On Local Adult RH 0 137930 16
Recollecti Local Adult RH 0 137792 16
Bradman's Local Adult RH 0 88216 16
One Step A Local Adult RH 0 67598 16
Acts Of Do Local Adult RH 0 66162 16
Anything S Local Adult RH 0 56571 16
Antartica Local Adult RH 0 50699 16
Australian Local Adult RH 0 45931 16
Captain Au Local Adult RH 0 42249 16
Lot of Cro Local Adult RH 0 42249 16
Snatched: Local Adult RH 0 32326 16
Broken Son Local Adult RH 0 27558 16
The Show G Local Adult RH 0 24245 16
Sheilas, W Local Adult RH 0 22036 16

So the ranking works nicely, and correctly resets itself on a change of pc_desc.
However, when it gets to records where the sales value = 0 they are all ranked as 16. Is there anyway I can then get the ranking to check budget values and continue ranking from 16 based on this column?
So the output looks like this:

svt_title pc_desc sales budget rank
---------- --------------- ----------- ----------- -----------
Bullo: The Local Adult RH 611142 294177 1
Incredible Local Adult RH 396012 275216 2
Dopeland Local Adult RH 156034 165406 3
Territory Local Adult RH 147955 183631 4
The Hamilt Local Adult RH 87008 110270 5
Good, The Local Adult RH 80830 48490 6
Shoot Stra Local Adult RH 66874 59148 7
Swift As D Local Adult RH 66129 44071 8
Fed Up Wit Local Adult RH 52210 55117 9
Why First Local Adult RH 42949 44071 10
Floaty Boy Local Adult RH 36707 33799 11
Swan Bay Local Adult RH 32447 29381 12
True Grit Local Adult RH 22469 24245 13
The Flambo Local Adult RH 17623 25349 14
Over The E Local Adult RH 17272 33799 15
A Pasty-Fa Local Adult RH 0 588355 16
Walking On Local Adult RH 0 137930 17
Recollecti Local Adult RH 0 137792 18
Bradman's Local Adult RH 0 88216 19
One Step A Local Adult RH 0 67598 20
Acts Of Do Local Adult RH 0 66162 21
Anything S Local Adult RH 0 56571 22
Antartica Local Adult RH 0 50699 23
Australian Local Adult RH 0 45931 24
Captain Au Local Adult RH 0 42249 25
Lot of Cro Local Adult RH 0 42249 26
Snatched: Local Adult RH 0 32326 27
Broken Son Local Adult RH 0 27558 28
The Show G Local Adult RH 0 24245 29
Sheilas, W Local Adult RH 0 22036 30

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-25 : 03:38:18

Try adding to this bit

quote:

and a.sales < b.sales

So it becomes

and a.sales < b.sales
and (and a.sales < b.sales or a.budget < b.budget)

-------
Moo.

Edited by - mr_mist on 06/25/2003 03:39:12

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-25 : 08:06:57

Mr. Mist --

I don't think what you posted will work -- you need to handle the case where a.sales = b.sales, which is the tie situation, and your logic doesn't allow for that -- a.sales must always be less than b.sales.

Take a look at this:

and a.sales < b.sales
or (and a.sales = b.sales and a.budget < b.budget)

That's how you would "rank" by more than one column. I.e., if you are sorting by A, B, and C you would say:

Where t1.A < t2.A2 OR
(t1.A = t2.A and t1.B < t2.B) OR
(t1.A = t2.A and t1.B = t2.B and t1.C < t2.c)

hopefully that logically makes sense...

- Jeff

Edited by - jsmith8858 on 06/25/2003 08:10:27

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-25 : 08:39:05

Ahh. Must. Test.

-------
Moo.

lee_h
Starting Member

36 Posts

Posted - 2003-06-25 : 19:58:04

cheers jeff,

just need to remove the and imediatly inside the brackets, and a bit of a bracketing to take into consideration the change of pc_desc, but it works a treat.

where a.pc_desc = b.pc_desc
and (a.sales < b.sales
or (a.sales = b.sales and a.budget < b.budget))

mr mist - I think I came out with the same solution as you, and was why I was getting myself lost. Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources