CODEWITHSUNDEEP
sqlselectsql-order-by
Cloud Chen
Cloud Chen

Reputation: 535

SQL query ordering in custom order

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?

Upvotes: 3

Views: 8691

Answers (2)

krokodilko
krokodilko

Reputation: 36137

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

Upvotes: 2

Declan_K
Declan_K

Reputation: 6826

Add a New Table CustomSeq with two columns, Value and Sequence. In that table you can store the values and their custom order. then join to that table and order by it's Sequence column.

Upvotes: 2

Related Questions