CODEWITHSUNDEEP
mysqlsql-order-by
Pradeep Singh
Pradeep Singh

Reputation: 3634

How to exclude some values in sorting with MySQL?

I have a table like this:

id products_name sort
1   abc           0 
2   xyz           1
3   pqr           2
4   qwe           0

I want to sort records through the sort column and in ascending order, but I don't want the rows with 0 at the top in the result set.
The rows having 0 in the sort column should be at the bottom of the result set, and the rest of the rows should be sorted in ascending order using the sort column.

How do I do this?

Upvotes: 9

Views: 7389

Answers (2)

Romain
Romain

Reputation: 12819

You could try something like:

ORDER BY IF(SORT=0, 999999999, SORT)

Upvotes: 12

MC Emperor
MC Emperor

Reputation: 23017

You can use the ORDER BY IF statement:

SELECT *
FROM table
ORDER BY IF(SORT = 0, 999999999, SORT)

or you can use the UNION ALL statement to achieve this:

(SELECT * FROM table WHERE sort > 0 ORDER BY sort)
UNION ALL
(SELECT * FROM table WHERE sort = 0)

Upvotes: 7

Related Questions