Order by multiple columns with-CodePudding

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

CodePudding user response：

You Need to derive a new column as per the required order using case statement and use that column to sort the result like below.

select Name,col1,col2,col3
from(
Select *,case when Name='John' then 1
              when Name='paul' then 2
              when Name= 'Greg' then 3
              when Name = 'Jane' then 4
              else 5
        end as sortid
from test
) a
order by sortid, Name

CodePudding user response：

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.