CODEWITHSUNDEEP
sqlsql-servert-sql
Moonstone
Moonstone

Reputation: 121

Sort by the largest value across multiple columns

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.

Upvotes: 3

Views: 702

Answers (2)

David Buttrick
David Buttrick

Reputation: 230

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.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176034

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

Upvotes: 5

Related Questions