Rang eines Elements in SQL Server

Question

In SQL Server 2008 R2 versuche ich, den Rang eines einzelnen Elements zu ermitteln, der sich aus der Anzahl der Vorkommen ergibt. Ich kann den Rang einer Reihe von Elementen ermitteln, z. B. die zehn häufigsten Vorkommen einer Farbe, aber ich kann den Rang einer bestimmten Farbe nicht ermitteln.

D.h. ich habe eine Tabelle mit Vehicles die einen Vehicles.ColourID Spalte.

Für eine bestimmte ColourID möchte ich den Rang ermitteln, den sie einnimmt.

d.h.

• ColourID 7 hat 10 Instanzen
• ColourID 10 hat 5 Instanzen
• ColourID 2 hat 1 Instanz

In dieser Ergebnismenge hat die Farbkennung 7 den Rang 1, die Farbkennung 10 den Rang 2 und so weiter.

Ich möchte eine Abfrage nach ColourID 10 durchführen und feststellen, dass sie den Rang 2 hat. Das ist es, womit ich zu kämpfen habe.

Ich habe versucht, abgeleitete Tabellen und allgemeine Tabellenausdrücke zu verwenden, aber in beiden Fällen ergibt sich ein Rang von 1.

Weiß jemand, wie ich den Rang eines einzelnen Artikels abfragen kann?

Answer 1

Um alle Farben zu ordnen:

;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

Sie können diese Technik dann erweitern, um den Rang eines einzelnen Elements abzufragen:

;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