Nerevar Nerevar - 7 months ago 23
SQL Question

"SQL ORDER BY" where 1 and 2 should be handled equal

I have a SELECT query in which I am sorting by several columns

SELECT * FROM tablex
WHERE
date = '$date' AND
type = '1'
ORDER BY
column_1 ASC,
CASE column_2 WHEN '00:00:00' THEN 'b' ELSE 'a' END,
column_2 ASC,
column_3 DESC


The values in column_1 can be the numbers from 1 to 4 and now I want the values '1' and '2' to be handled equal. So they should be handled as if they're all '1' e.x.

Thx for your help.

Answer

You should first handle the case that they are 1 or 2, use a CASE:

SELECT * FROM tablex
WHERE
    date = '$date' AND
    type = '1'
ORDER BY
    CASE WHEN column_1 IN(1, 2) THEN 0 ELSE column_1 END ASC,
    CASE column_2 WHEN '00:00:00' THEN 'b' ELSE 'a' END,
    column_2 ASC,
    column_3 DESC
Comments