Is there a way in MySQL to choose an order for the beginning and end of rows returned?

Question

Look at this simple query

SELECT id FROM users

Now, say I wanted to have users with ids ordered 4, 5, 7, 8, then I don't care about the middle, and finally I want the last ones to be 55, 56, 58.

So the returned rows would be something like this (\n a new row)

4  // explicitly chose these as the start rows
5  // " "
7  // " "
8  // " "
44 // these just come in any order (order not important)
23 // " "
10 // " "
55 // explicitly chose these as the end rows
56 // " "
58 // " "

How can I modify the query above to do this? Thanks

Answer 1

Use:

  SELECT u.id
    FROM USERS u
ORDER BY CASE id
           WHEN 4 THEN 1
           WHEN 5 THEN 2
           WHEN 7 THEN 3
           WHEN 8 THEN 4
           WHEN 55 THEN 100000
           WHEN 56 THEN 100001
           WHEN 58 THEN 100002
           ELSE id
         END ASC

Set the values far outside actual values to get 55/56/58 in order at the end.

Answer 2

It's better to introduce an order field in your table instead of using a hack or anything hard-coded.