CODEWITHSUNDEEP
sql-server
George Appiah Sarfo
George Appiah Sarfo

Reputation: 105

SQL Server get rank of one column amongst columns

I want an SQL Server query to rank the Black Column across rows, and display as Black Rank. Please assist.

Area  Blue  Green  Red  Yellow  Brown  Black  Black Rank
A      514     13  211    1059    544    659           2
B      329     16  172     622    386    362           3
C      497     14  246     921    610    553           3
D      652     18  279    1243    804    748           3
E     1485     22  555    4171   1055   2688           1
F      470      5  114    1396    304   1311           2
G      401     10  196    1170    548    590           2
H      626      6  210    1488    511    889           2
J      417      3   61     995    132    822           2
K      247      2   24     746     62    672           2
L      172   NULL   19     444     57    452           1
M      201      6  107    1113    269    818           2
N      122      4   77     567    231    388           2
O      137      3   84     237    247    241           2
P      133      1   82     345    222    251           2

Upvotes: 2

Views: 1213

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

Anon's solution works well, here is just another alternate version using SQL Server analytic functions;

WITH cte AS (
 SELECT Area, name, value, RANK() OVER (PARTITION BY Area ORDER BY value DESC) r 
 FROM mytable 
 UNPIVOT(value FOR name IN (Blue, Green, Red, Yellow, Brown, Black))q
)
SELECT mytable.*, r [Black rank]
FROM mytable
JOIN cte ON cte.area = mytable.area 
WHERE cte.name = 'black';

Upvotes: 0

Anon
Anon

Reputation: 10918

SELECT *
FROM MyTable
CROSS APPLY (
  SELECT COUNT(*) AS [Black Rank]
  FROM (VALUES (Blue),(Green),(Red),(Yellow),(Brown),(Black) ) t(val)
  WHERE val >= Black
) b

Upvotes: 2

Related Questions