Ranking results by category. - 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)

Ranking results by category.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-23 : 08:56:52

Salim writes "I'm trying to compose a single SQL statement that will take an unorderd list of items in a category and sort them according to their category and rank within a category... the catch is, I dont much care about what score an item got, just it's position in the rank.

Type, Name, Score

Apple, Cox's, 4
Citrus, Satsuma, 7
Citrus, Grapefruit, 4
Apple, Braburn, 5
Apple, GSmith, 8
Citrus, Lemon, 1
Citrus, Lime, 0
Citrus, Tangerine, 9
Apple, Cooking, 2
Misc, Pineapple, 5

The result of the query should look like this:

Type, Name, Rank
Apple, GSmith, 0
Apple, Braburn, 1
Apple, Cox's, 2
Apple, Cooking, 3
Citrus, Tangerine, 0
Citrus, Satsuma, 1
Citrus, Grapefruit, 2
Citrus, Lemon, 3
Citrus, Lime, 4
Misc, Pineapple, 0

The list has been sorted by 'type' and then by 'Score' descending.

The 'Rank' column can be computed as the numer of items in the same category with a higher score than the current item.

To make matters harder, this must be implemented as a single SELECT statement - sub-selects and set-operators are allowed.

Sal"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-23 : 09:45:01


select
    type,
    name,
    (select count(47)
     from salim
     where type = s.type and
           score < s.score) as rank
from
    salim s
order by
    type,
    score

You haven't specified how you will deal with ties....

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources