Ranking subsets of data
-
I have a table that i need to rank i have looked in BOL but ranking is not something i can find much information on! I need to rank different subsets of the data. I have an example of the type of thing i am looking for below, the rank is based on the value and should be based on each PostDistrict. If you need any more information let me know. I am using SQL 2000 SP3
ID PostDistrict Type Value Rank
1 LE1 English 17 3
2 LE1 French 22 1
3 LE1 Spanish 19 2
4 LE1 German 13 4
5 LE2 English 25 1
6 LE2 French 18 2
7 LE2 Spanish 15 4
8 LE2 German 18 2
-
Hi John,
See http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx - point 13.
Here's that technique applied to your situation...
--data
declare @t table (ID int, PostDistrict varchar(10), Type varchar(10), Value int)
insert @t
select 1, 'LE1', 'English', 17
union all select 2, 'LE1', 'French', 22
union all select 3, 'LE1', 'Spanish', 19
union all select 4, 'LE1', 'German', 13
union all select 5, 'LE2', 'English', 25
union all select 6, 'LE2', 'French', 18
union all select 7, 'LE2', 'Spanish', 15
union all select 8, 'LE2', 'German', 18
--calculation
select *, (select count(*) + 1 from @t where PostDistrict = a.PostDistrict and Value > a.Value) as Rank from @t a
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
That looks like it will give me the desired result i will test it this afternoon. Thanks a lot for such a quick response.
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply