CODEWITHSUNDEEP
mysqlsql
Ergec
Ergec

Reputation: 11824

Getting and ordering rows with value greater than zero then rows with value zero

I have a table like this

id    title    display_order
1     t1       3
2     t2       1
3     t3       5
4     t4       4
5     t5       2
6     t6       0
7     t7       7
8     t8       6
9     t9       0
10    t10      0

What I need is to have results like this

id    title    display_order
2     t2       1
5     t5       2
1     t1       3
4     t4       4
3     t3       5
8     t8       6
7     t7       7
...order of the rest is not important but should be in the result
6     t6       0
9     t9       0
10    t10      0

I can get this result with two SQL queries and then combine them.

Is there a way to do this with one SQL?

Thanks

Upvotes: 10

Views: 7149

Answers (3)

Andriy M
Andriy M

Reputation: 77707

SELECT *
FROM atable
ORDER BY
  display_order = 0,
  display_order

When display_order is 0, the first sorting term, display_order = 0, evaluates to True, otherwise it evaluates to False. True sorts after False – so, the first sorting criterion makes sure that rows with the display_order of 0 are sorted at the end of the list.

The second ORDER BY term, display_order, additionally specifies the order for rows with the non-zero order values.

Thus, the two criteria give you the desired sorting order.

Upvotes: 21

Hanlet Escaño
Hanlet Escaño

Reputation: 17380

Here is the solution in T-SQL in case anyone needs it

SELECT * FROM Table ORDER BY CASE WHEN display_order = 0 THEN display_order END ASC,CASE WHEN display_order > 0 THEN display_order  END ASC

Upvotes: 2

Shakti Singh
Shakti Singh

Reputation: 86406

Try the below one-

SELECT * FROM table_name 
ORDER BY IF (display_order = 0, 9999999, display_order)

Upvotes: 3

Related Questions