How to rank records based on the maximum value across in a field?

Question

Data in the table

ID  attempt1  attempt2   attempt3
1      2.00      2.10      1.99
2      2.15      2.01      1.80
3      1.85      2.05      1.98

Expected result

ID  attempt1  attempt2   attempt3   rank
2      2.15      2.01      1.80       1
1      2.00      2.10      1.99       2
3      1.85      2.05      1.98       3

Query to achieve result

select ID, attempt1, attempt2, attempt3, ROW_NUMBER() OVER(ORDER BY attempt1, attempt2, attempt3) 
AS rank FROM Attempts

The query above works fine when I attempt to rank the values in one column say attempt1, but when I add the additional fields the ranking does not work the correct way. I am not sure what you be causing this. I tried the following solution Multiple columns in OVER ORDER BY but it does not seem to work.

Gordon Linoff · Answer 1 · 2018-02-16T04:07:12.277

1

You need to calculate the maximum value across the fields.

In SQL Server 2008, I think I would do:

select t.*,
       row_number() over (order by v.max_attempt desc) as seqnum
from attempts t cross apply
     (select max(attempt)
      from (values (t.attempt1), (t.attempt2), (t.attempt3)) v(attempt)
     ) v (max_attempt);

edited Feb 16 '18 at 04:07

answered Feb 15 '18 at 14:59

Gordon Linoff

1,242,037
58
646
786

Is this solution applicable to sql server 2012 – Andre Reid Feb 15 '18 at 15:18
The DESC key word is missing form the order by clause. Adding the DESC keyword solved the problem. – Andre Reid Feb 15 '18 at 15:32

score 1 · Accepted Answer · answered Feb 15 '18 at 15:03

You need to derive the "max score for that row" outside of the ROW_NUMBER() function.

What you've written so far orders the rows by attempt1, then where there are tyies, order by attempt2, and where there are still ties, order by attempt3.

The query below uses a sub-query in an APPLY so as to use MAX() across three columns in a single row. The result of which can then be used in the ROW_NUMBER().

SELECT
  attempts.ID,
  attempts.attempt1,
  attempts.attempt2,
  attempts.attempt3,
  ROW_NUMBER() OVER (ORDER BY max_score.val DESC)   AS rank
FROM
  attempts
CROSS APPLY
(
  SELECT MAX(score) AS val
    FROM (VALUES (attempts.attempt1),
                 (attempts.attempt2),
                 (attempts.attempt2)) attempt(score)
)
  max_score

Good solution and thanks for the explanation to aid my understanding. — Andre Reid, Feb 15 '18 at 15:33

How to rank records based on the maximum value across in a field?

2 Answers2