Table column sorting based on NULL Value

Question

I want to sort column2 based on Column1 values. I want split column1 data based on NULL value. The final query result will be displayed on evaluating the values in the column1

   Column1    Column2 
    NULL         100
    NULL          60
    NULL          90
    10            22
    20            40
    05            35
    15            20
    40            10
    30            25
    20            30

Would become:

   Column1     Column2 
    20            40
    05            35
    20            30
    30            25
    10            22
    15            20
    40            10
    NULL         100
    NULL          90
    NULL          60

Thanks

Which DBMS are you using? Postgres? Oracle?

user330315
– user330315

2017-02-23 09:48:06 +00:00
Commented Feb 23, 2017 at 9:48 — user330315
– user330315, Commented Feb 23, 2017 at 9:48

Gurwinder Singh · Accepted Answer · 2017-02-23 09:47:32Z

5

You can use case in your order by clause like this:

Select * from t
Order by case when column1 is null then 1 else 0 end, column2 desc

answered Feb 23, 2017 at 9:47

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JohnHC · Accepted Answer · 2017-02-23 09:46:54Z

2

Use a case in your order by

ORDER BY case when column1 is null then 1 else 0 end, column2 desc

answered Feb 23, 2017 at 9:46

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

Comments

Jens · Accepted Answer · 2017-02-23 09:55:16Z

2

SELECT 1 AS Grp, column2
FROM T 
WHERE column1 IS NOT NULL
UNION ALL
SELECT 2 AS Grp, column2 
FROM T 
WHERE column1 IS NULL
ORDER BY Grp, column2;

This an alternative to GurV's approach, where this can actually benefit from indexes.

GurV's approach:

Union approach:

edited Feb 23, 2017 at 9:55

answered Feb 23, 2017 at 9:50

Jens

3,3092 gold badges28 silver badges43 bronze badges

Collectives™ on Stack Overflow

Table column sorting based on NULL Value

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related