mySQL order by certain values & then DESC?

Question

Say I have a mySQL table which has a field named "name"

This field contains one name per row.

There are 10 rows:

Apple
Pear
Banana
Orange
Grapefruit
Pineapple
Peach
Apricot
Grape
Melon

How would I form a query so I select from the table, I want the details to show like this:

Peach
Apple
Apricot
... and then everything else is listed by descending order.

So basically I want to show a few specified results at the TOP of the list, and then the rest of the results in descending order.

Thank you.

Answer 1

You could do something like this:

select *
from fruit
order by
    case name
        when 'Peach'   then 1
        when 'Apple'   then 2
        when 'Apricot' then 3
        else 4
    end,
    name

That should work in any SQL database.

Answer 2

You can sort with conditionals using the IF() function. The documentation for IF() states:

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

So you can use it to sort specific elements at the top like this:

SELECT *
FROM fruit
ORDER BY
    IF(name = 'Peach', 0, 1),
    IF(name = 'Apple', 0, 1),
    IF(name = 'Apricot', 0, 1),
    name DESC

This is a series of orders, with the first taking highest precedence. So if name='Peach', the value will be 0, for all others the value will be 1. Since in default ASC order, 0 comes before 1, this ensures "Peach" will be at the top of the list. The second sort in the series specifies how to break ties for the first sort. In this case, all elements exccept 'Peach' tie in the first sort with the value '1'. And in this case, 'Apple' gets pushed to the top of the tie list, which in reality is the second spot in the total list. Etc... down to the end the last 'name DESC'.

As the other answer points out, CASE() is an alternative to a series of IF()s:

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

The first version returns the result where value=compare_value. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.