Order by multiple columns with

Question

I'm trying to sort a table by multiple columns, but I should shift the most important column.

Given a sample like:

Name   Col1 Col2 Col3
Paul      1    2    1
John      1    1    1
Greg   NULL    2    1
Jane      2    2    1

I would like the data to be sorted like John, Paul, Greg and Jane.

If I do ORDER BY Col1, Col2, Col3, Greg is the first result because of NULL. What I'm trying to do is to "shift" the columns, to discard all the NULL values

Edited:

The desired result is

• John
• Paul
• Greg
• Jane

Greg ends up third because the query should remove the null value, working on a "fixed" version like

Name   Col1 Col2 Col3
Paul      1    2    1
John      1    1    1
Greg      2    1    -  <== Greg cols moved to the left by one step
Jane      2    2    1

Edit2:

I think that something like the COALESCE function should help. If I run:

SELECT Name, COALESCE(Col1, Col2, Col3, 0) FROM Table1

The result is:

Paul  1
John  1
Greg  2
Jane  2

Answer 1

Use CASE expressions in the ORDER BY clause to check whether a column is null and needs to be shifted:

SELECT *
FROM tablename
ORDER BY COALESCE(Col1, Col2, Col3),
         CASE 
           WHEN Col1 IS NOT NULL THEN COALESCE(Col2, Col3) 
           WHEN Col2 IS NOT NULL THEN Col3 
         END,
         CASE WHEN Col1 IS NOT NULL AND Col2 IS NOT NULL THEN Col3 END;

See the demo.