CODEWITHSUNDEEP
mysqlsql
Zhang Xianwei
Zhang Xianwei

Reputation: 23

MySQL Order By a column ASC/DESC related to another Column

I have two columns in database like this:

ColumnA ColumnB
NULL    1
2       1
NULL    3
1       3
NULL    2
1       2

I want to:

First step， sort columnA placing the NULL rows on the top, then the NotNull rows. So it should be:

NULL     1
NULL     3
NULL     2
2        1
1        3
1        2

Second step (what I want to get at the end), sort columnB, asc columnB when columnA is Null but desc columnB when columnA is NotNull, so it should be:

NULL     1
NULL     2
NULL     3
1        3
1        2
2        1

So, how can I make a single mysql query to get it there?

Upvotes: 2

Views: 743

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269439

I believe you want:

select a, b
from t
order by (a is null) desc,  -- put null values on top
         (case when a is null then b end) asc,
         b desc;

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

How about:

select a,b from t order by a, case when a is null then b end, b desc

or maybe...

SELECT a,b FROM t ORDER BY a,CASE WHEN a IS NULL THEN b* -1 ELSE b END DESC;

Upvotes: 1

Related Questions