Sort by the largest value across multiple columns

Question

What would be the most efficient way to sort a query by looking at the largest column value in each row?

For example:

Dan | 5 | 0 | 3
Moe | 0 | 9 | 2
Joe | 3 | 3 | 8

Sorting this dataset should return: Moe, Joe, Dan

My dataset is more complicated than above so I am trying to avoid any combination of subqueries with group by max.

Lukasz Szozda · Accepted Answer · 2018-06-20T19:30:41.917

5

You could use CROSS APPLY:

SELECT *
FROM tab t
CROSS APPLY (SELECT MAX(col) c FROM (VALUES(t.col1),(t.col2),(t.col3)) sub(col)) s
ORDER BY s.c DESC;

DBFiddle Demo

Output:

┌──────┬──────┬──────┬──────┬───┐
│ name │ col1 │ col2 │ col3 │ c │
├──────┼──────┼──────┼──────┼───┤
│ Moe  │    0 │    9 │    2 │ 9 │
│ Joe  │    3 │    3 │    8 │ 8 │
│ Dan  │    5 │    0 │    3 │ 5 │
└──────┴──────┴──────┴──────┴───┘

EDIT

Great answer. I don't suppose there's a way to do this for an arbitrary number of columns (without dsql) is there?

There is a way without dynamic SQL:

SELECT DISTINCT t.*, c.s
FROM tab t
CROSS APPLY (VALUES(CAST((SELECT t.* for XML RAW) AS xml))) B(XMLData)
CROSS APPLY (SELECT MAX(a.value('.','INT') ) s
             FROM B.XMLData.nodes('/row')  AS C1(n)
             CROSS APPLY C1.n.nodes('./@*') AS C2(a)
             WHERE a.value('local-name(.)','varchar(100)') LIKE 'col%'
                     -- filtering based on name
                     -- it is also possible to JOIN and filter 
                     -- based on metadata like sys.columns
               ) C
ORDER BY s DESC;

DBFiddle Demo

edited Jun 20 '18 at 19:30

answered Jun 20 '18 at 19:17

Lukasz Szozda

162,964
23
234
275

Great answer. I don't suppose there's a way to do this for an arbitrary number of columns (without dsql) is there? – Xedni Jun 20 '18 at 19:24
1

@Xedni Hmm, great question, I guess it is possible with XML trick. Similar to [SELECT rows that have specified values in one of the columns](https://stackoverflow.com/a/50850632/5070879) – Lukasz Szozda Jun 20 '18 at 19:25
1

@Xedni Yes, it is possible. Please check my demo – Lukasz Szozda Jun 20 '18 at 19:30
ty Lukasz. I'll have to remember this one – Xedni Jun 20 '18 at 19:32
Is there a way to place the cross apply query in a view and then reference it an ORDER BY clause later? – Moonstone Jun 20 '18 at 19:34
1

Do you want: `CREATE VIEW my_view AS SELECT * FROM tab t CROSS APPLY (SELECT MAX(col) c FROM (VALUES(t.col1),(t.col2),(t.col3)) sub(col)) s` and then `SELECT * FROM my_view ORDER BY c DESC`? – Lukasz Szozda Jun 20 '18 at 19:35
Yes but I get: Invalid column name 'c' – Moonstone Jun 20 '18 at 19:36
@Moonstone Please check your query: **[DEMO](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=007cf72b9b178e3999b1488192bb1ecd)** – Lukasz Szozda Jun 20 '18 at 19:37
Thanks got it. I didn't add s.c to the view's select statement. – Moonstone Jun 20 '18 at 19:42

score 0 · Answer 2 · answered Jun 20 '18 at 19:33

I almost never sort on the database. In this case, where the sorting is complicated, and more complex than the model you presented, I typically take the data out of the database and sort it in code.

I have always been rewarded by increased performance when I do this. Its much cheaper to sort 8000000 rows in memory than it is in the database. Since your data model is complex, writing an algorithm to sort this is more flexible than having to write sql - you can write code that is resistant to schema change, and will continue to obey your rules.

Sort by the largest value across multiple columns

2 Answers2

EDIT