Ordering Select clause result in specific way

Question

I need help with writing a select clause query.

For example, lets say I have a query like that:

select value from some_table order by value asc;

as a result I get this:

1
2
3
4
5
6
7
8
9
10

but a special query I want to write, is the one which still will give me sorted values, but will put 5 after 8.

this means I need one value to be out of regular order.

it can be described in other way. lets say I have two groups of numbers (example): A={ a | 1<=a<=118, a!=78 } B={ b | b>118 } I have a group C=A U B U {78} and I need all these values sorted like "A,78,B"

Answer 1

Assuming value is integer, you could do this:

SELECT *
  FROM tbl
ORDER BY
       CASE
         WHEN value = 5 THEN 8.5
         ELSE value
       END

Answer 2

Order by some CASE-expression to remap your values.

Answer 3

You can use multiple conditions in your order by:

ORDER BY (value BETWEEN 1 AND 118) AND value != 78 DESC,
         value > 118 DESC,
         value

This will ensure that values which match the first predicate come first, then values matching the second predicate, and finally values matching none of the predicates. If there is a tie (two numbers matching the same predicate) then these numbers are sorted in ascending order.

Note that I haven't tested this in Oracle. It might be necessary to wrap the predicate in a CASE expression (CASE WHEN predicate THEN 1 ELSE 0 END) to get the sorting to work in Oracle.

ORDER BY
    (CASE WHEN ((value BETWEEN 1 AND 118) AND value <> 78) THEN 1 ELSE 0 END) DESC,
    (CASE WHEN (value > 118) THEN 1 ELSE 0 END) DESC,
    value

Answer 4

Or to expand upon DCP's answer...

SELECT * 
  FROM tbl 
ORDER BY 
       CASE 
         WHEN (Condition for first grouping) THEN 1
         WHEN (Condition for second grouping) THEN 2
         WHEN (Condition for third grouping) THEN 3
         ELSE 4
       END