SQL query ordering in custom order

Question

I have a custom ordering need like this:

normal ordering  |  custom ordering 
      1          |        7
      2          |        6
      3          |        5
      4          |        4
      5          |        3
      6          |        2
      7          |        8
      .          |        .
      .          |        .
      .          |        .
      .          |        .
      .          |        N
      N          |        1

I have thought about using UNION to combine 3 different select queries with the help of ORDER BY and LIMIT. However, I can not do that because UNION have to be used before ORDER BY and LIMIT.

How can I make a selection (or selections) to achieve the custom ordering above?

Another workaround might help is just make the 1st record returned in this select query the last record, but how?

Answer 1

Try this:

SELECT x
FROM t1
ORDER BY
    CASE
      WHEN x = 1 THEN 100000001
      WHEN x between 2 and 7 THEN 7 - x
      WHEN x between 8 and ( SELECT max(x) FROM t1 ) - 1 THEN x
      ELSE 100000000 
    END 

100000000 constans must be greather than N.
Here is a simple demo