user6650650 user6650650 - 3 months ago 24
MySQL Question

sql order by logic

I have a table that contains two columns. One of the columns contain text and the other contain integer values.

I need this table to be ordered by the integer value (higher values to the top) but if the integer value equals to 0 then I need that row to be ordered alphabetically aswell. Lets say that I have this table

TextCol|IntCol|
-------|------|
Delta | 0 |
Alpha | 0 |
Beta | 3 |
Sierra | 2 |
Gama | 1 |


Now I need this :

TextCol|IntCol|
-------|------|
Beta | 3 |
Sierra | 2 |
Gama | 1 |
Alpha | 0 |
Delta | 0 |


What would be the SQL query for this?

Answer

You can use the following solution using the CASE WHEN:

SELECT * FROM table 
ORDER BY intColumn DESC, 
         CASE WHEN intColumn = 0 THEN stringColumn END ASC

Here you can find a example: http://sqlfiddle.com/#!9/a982a/1

Comments