Rankings - 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 2005 Forums

Transact-SQL (2005)

Rankings

Author

Topic

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-10-09 : 01:24:01

Hi EveryOne,
Can anyone help me out here please. How do i get the required result. I tried using
Rank() over() but the result is not what I want. The rank shold be the heighest sales figure.

Thanks to all

Declare @Total_Sales Table
(
[Name] varchar(20),
Sales int
)
insert into @Total_Sales
select 'John', 10 union all
select 'Jennifer',15 union all
select 'Stella', 20 union all
select 'Sophia', 40 union all
select 'Greg', 50 union all
select 'Jeff', 20

The Result I require should be
Name Sales Sales_Rank
-------------------- ----------- -----------
Greg 50 1
Sophia 40 2
Stella 20 3
Jeff 20 3
Jennifer 15 5
John 10 6

(6 row(s) affected)

Sachin.Nand

2937 Posts

Posted - 2010-10-09 : 01:49:05

select *,rank()over(order by sales desc)Sales_Rank from yourtable

PBUH

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-10-10 : 00:35:37

quote:
Originally posted by Sachin.Nand

select *,rank()over(order by sales desc)Sales_Rank from yourtable

PBUH

Thanks Sanhin. I was partitioning the column that is why I wasnt getting the
required result. You are a genious.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources