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?

krokodilko · Accepted Answer · 2013-08-14 18:04:40Z

2

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

edited Aug 14, 2013 at 18:04

answered Aug 14, 2013 at 17:57

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cloud Chen Over a year ago

Thanks for your aid. IMHO, using the hard-coded "100000001" might cause problem if the records gets larger and larger in the future?

ErTR Over a year ago

What about with the DISTINCT query?

Declan_K · Accepted Answer · 2013-08-13 15:31:43Z

2

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.

edited Aug 13, 2013 at 15:31

answered Aug 13, 2013 at 15:20

Declan_K

6,8162 gold badges22 silver badges31 bronze badges

Collectives™ on Stack Overflow

SQL query ordering in custom order

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related