How to Rank Sql server Records

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)

How to Rank Sql server Records

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2010-07-08 : 09:00:37

Hi,

I want to rank each record using the Number of sales in the below
table,

CREATE TABLE #temp
(
agentid INT,
fname VARCHAR(50),
lname VARCHAR(50),
sales INT
)

INSERT INTO #temp
SELECT 1,'FONE','LONE',22
UNION ALL
SELECT 2,'FTWO','LTWO',123
UNION ALL
SELECT 5,'FFIVE','LFIVE',2
UNION ALL
SELECT 4,'FFOUR','LFOUR',0
UNION ALL
SELECT 3,'FTHREE','FTHREE',2

SELECT
t1.AGENTID,
t1.FNAME,
t1.LNAME,
t1.SALES,
COUNT(t2.SALES) SALES_RANK
FROM
#temp t1,#temp t2
WHERE
t1.sales <= t2.sales
GROUP BY
t1.AGENTID,
t1.FNAME,
t1.LNAME,
t1.SALES
ORDER BY
t1.SALES
DESC

but am not getting output accurately if we have the
same No. of sales for more than one recods...getting the result as
shown below

AGENTID FNAME LNAME SALES SALES_RANK
2 FTWO LTWO 123 1
1 FONE LONE 22 2
3 FTHREE LTHREE 2 4
5 FFIVE LFIVE 2 4
4 FFOUR LFOUR 0 5

Thanks,
Vision

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-08 : 09:07:34

What is your expected result?

Madhivanan

Failing to plan is Planning to fail

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2010-07-08 : 09:17:12

Results should be like shown below:
Row one have 123 sales so Sales_Rank should be 1,
Row Two have 22 sales so Sales_Rank should be 2,
Row Three and Four have 2 sales so Sales_Rank should be 3 for both the records, but in my result set am getting Rank as 4 for both the records....

Expected OUtput:

AGENTID FNAME LNAME SALES SALES_RANK
2 FTWO LTWO 123 1
1 FONE LONE 22 2
3 FTHREE LTHREE 2 3
5 FFIVE LFIVE 2 3
4 FFOUR LFOUR 0 4

Sachin.Nand

2937 Posts

Posted - 2010-07-08 : 09:38:08

[code]
select *,DENSE_RANK()over(order by sales desc)as sales_rank from #temp
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources