Ranking in SQL 2000 - 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

Transact-SQL (2000)

Ranking in SQL 2000

Author

Topic

VBChick
Starting Member

1 Post

Posted - 2008-11-03 : 19:39:32

I wrote a ranking query utilizing the advice from Michael Valentine Jones in the following post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48441. I would post my query, but it's simply a rewrite of Michael's suggestion utilizing the tables in my database. The query works wonderfully well when the query is in descending order, and I use it to get the top performers. And, I thought it would work equally as well when sorted in ascending order to get the bottom performers. But, the ranking doesn't work the same when the table is sorted ascending.

So, for descending order I get data that looks like this
Region Qty Rank
North 5 1
North 4 2
North 3 3
South 11 1
South 10 2

But, when I sort the query in ascending order I get data that looks like this:
Region Qty Rank
North 3 2
North 4 3
North 5 4
South 10 18
South 11 19

Any ideas? Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-04 : 01:24:25

[code]DECLARE @Sample TABLE
(
Region CHAR(5) NOT NULL,
Qty TINYINT NOT NULL
)

INSERT @Sample
SELECT 'North', 5 UNION ALL
SELECT 'North', 4 UNION ALL
SELECT 'North', 3 UNION ALL
SELECT 'South', 11 UNION ALL
SELECT 'South', 10

-- Ascending
SELECT s1.Region,
s1.Qty,
(SELECT COUNT(*) FROM @Sample AS s2 WHERE s2.Region = s1.Region AND s2.Qty <= s1.Qty) AS Rank
FROM @Sample AS s1
ORDER BY s1.Region,
3

-- Descending
SELECT s1.Region,
s1.Qty,
(SELECT COUNT(*) FROM @Sample AS s2 WHERE s2.Region = s1.Region AND s2.Qty >= s1.Qty) AS Rank
FROM @Sample AS s1
ORDER BY s1.Region,
3 DESC[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources