July 11, 2011 at 9:21 am
I'm hoping there is a simple solution for my problem, I am trying to use the Ranking function in SQL Server 2005 but I need my results to come back as follows....
If I have a set like this
PersonSales Amt. Rank
Greg100 1
Phil100 1
Mike75 2
Joe75 2
Jeff50 3
Clay10 4
George 8 5
As you can see I need a relative ranking on this dataset so that the next sequence after a tie is the next number.
Can anyone show me how I can do this using T-SQL in 2005?
Thanks in advance for your help.
July 11, 2011 at 9:28 am
Check out the dense_rank() function
--select
--'Greg' as Person, 100 [Sales Amt]
--into #TestData
--union
--select 'Phil', 100
--union
--select 'Mike', 75
--union
--select 'Joe', 75
--union
--select 'Jeff', 50
--union
--select 'Clay', 10
--union
--select 'George', 8
select * from #TestData
select Person, [Sales Amt], dense_rank() over (order by [sales amt] desc )
from #TestData
July 11, 2011 at 9:33 am
Thank you so much!!!!
That's exactly what I needed.
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply