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

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