issue with ranking - 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

General SQL Server Forums

New to SQL Server Programming

issue with ranking

Author

Topic

sanp
Starting Member

2 Posts

Posted - 2010-10-26 : 16:13:17

i am new to sql server 2008, i need to find top 2 ranked companies in each state how would we do that, it would be great if someone could help me.
following is a sample data

state rank company description
co, 40, a, 'fdfd'
co, 30, b, 'sdff'
co, 100, c, 'fdfd'
co, 134, a, 'dfd'
az, 49, d, 'fdfd'
az, 44, e, 'fdfd'
az, 44, b, 'fdfd'
az,32,c,'fsdfsd'
ca, 3, a, 'fdfd'
ca, 2, b, 'fdfd'
ca,34,d, 'fdfd'

so for the output i would need top 2 rank from each state, if there are 2 companies with same rank they should be included
output

az, 32,c, 'fsdfsd',1
az, 44, e, 'fdfd',2
az, 44, b, 'fdfd',2

ca, 2, b, 'fdfd',1
ca, 3, a, 'fdfd',2

co, 30, b, 'sdff',1
co, 40, a, 'fdfd',2

Any help should be great, i am pretty new to this
thanks.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-10-26 : 17:32:24

WITH myCTE AS
(
SELECT
[State]
, [rank]
, [company]
, [description]
, RANK() OVER(ORDER BY [State], [rank]) AS ranking
FROM
sometable
)

SELECT
*
FROM
myCTE
WHERE
(ranking < 3)
;

sanp
Starting Member

2 Posts

Posted - 2010-10-27 : 18:33:13

txs for a quick reply, i resolved the problem using dense rank, thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources