ORDER BY condition

Question

I would like to retrieve an ordered query result, but I need to have some specific row(s) to be in front of the list. Something like here on Stack Overflow, in the list of answers the right answer is always the first one.

Assumed I need to have the rows with IDs 1,2,3 to be the head, the rest sorted by a date field, is it possible to do something like:

SELECT * FROM foo ORDER BY id IN (1,2,3), created_date

If not what is more efficient? There will be many rows!

SELECT *, 0 AS head FROM foo WHERE id IN (1,2,3) 
UNION
SELECT *, 1 AS head FROM foo WHERE id NOT IN (1,2,3)
ORDER BY head, created_date

or

SELECT *, IF(id IN (1,2,3), 0, 1) AS head
ORDER BY head, created_date

(I use MySQL now, but I'm interested in any other SQL solution.)

Solution 1

Your first example looks almost there to me.

SELECT * FROM foo ORDER BY id IN (1,2,3) DESC, created_date ASC

Added DESC because id IN (1,2,3) returns 1 if true or 0 if false. 1 > 0, so ordering them in descending order gets the desired result.

Added ASC because I like to be explicit.

Based on your later examples, I think what you're missing is that, though it contains spaces, field IN (list) is a single operator that returns 0 or 1. IF(id IN (1,2,3), 0, 1) is inherently redundant.

As such, you shouldn't need to use a UNION or sort manually, since MySQL makes this simpler than you even realize :)

Solution 2

UNION means UNION DISTINCT and this is relatively slow as it will check for duplicates even though there will not be any. You want UNION ALL:

SELECT *, 0 AS head FROM foo WHERE id IN (1,2,3) 
UNION ALL
SELECT *, 1 AS head FROM foo WHERE id NOT IN (1,2,3)
ORDER BY head, created_date

I would imagine that after this change there is not much difference in performance between the three queries. The best way to be sure is to measure it.