SQL SELECT Sort by specific row

Question

Suppose I have a table food, which I query SELECT name FROM food ORDER BY name:

| name
|--------
| Apple
| Banana
| Carrot
| Donut
...

I wish to specify that specific items of my choice be pinned to the top (e.g. "Lemon" and then "Carrot"), if they are in the table. Like so:

| name
| -------
| Lemon
| Carrot
| Apple
| Banana
| Donut
...

What kind of SQL query can I use to get this specific sort?

Answer 1

You can use a case statement in your order by clause to prioritize items with a certain name.

The following will put Lemon and Carrot in priority order by assigning them the values of 1 and 2 from the case, where all others will get the value 3. Those remaining that were assigned 3 will then be sorted by the second expression in the order by clause, which is just the name column.

SELECT *
FROM food
ORDER BY
  CASE name
    WHEN 'Lemon' THEN 1
    WHEN 'Carrot' THEN 2
    ELSE 3
  END,
  name

Answer 2

Make a look up table like this:

WITH odering(F, Ord) AS
(
   VALUES ('Lemmon', 2),
          ('Carrot', 1)
)
SELECT name
FROM table t
LEFT JOIN ordering Ord on T.name = Ord.name
ORDER BY COALESCE(Ord.Ord, 0) DESC, Name ASC