CODEWITHSUNDEEP
sql-serversql-server-2008statistics
Amethi
Amethi

Reputation: 1166

Rank of an item in SQL Server

In SQL Server 2008 R2, I'm trying to work out the rank of an individual item, as determined by the number of occurances. I can work out the rank of a number of items, i.e. the top ten instances of a colour, but I can't get the rank of a specific colour.

I.E I have a table of Vehicles, which has a Vehicles.ColourID column.

For a specific ColourID I want to determine the rank of it.

i.e.

In that result-set, ColourID 7 has a rank of 1, ColourID 10 has a rank of 2 and so on.

I would like to query for ColourID 10 and find it has a rank of 2. This is what I am struggling with.

I've tried using derived tables and common table expressions, but in both cases they bring back a rank of 1.

Does anyone know how I could query for the rank of a single item?

Upvotes: 2

Views: 225

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

To rank all the colours:

;with cteCount as (
    select ColourID, count(*) as ColourCount
        from Vehicles
        group by ColourID
)
select ColourID, ColourCount, 
       row_number() over (order by ColourCount desc) as Rank
    from cteCount

You can then extend this technique to query for the rank of a single item:

;with cteCount as (
    select ColourID, count(*) as ColourCount
        from Vehicles
        group by ColourID
),
cteRank as (
    select ColourID, ColourCount, 
           row_number() over (order by ColourCount desc) as Rank
        from cteCount
)
select ColourID, ColourCount, Rank
    from cteRank
    where ColourID = 10

Upvotes: 1

Related Questions