I am working on MYSQL 5.6 and I have following integer values:
1 2 3 21 22 23 0
Wants to sort hour wise and wants to show my results in this format:
3 2 1 0 23 22 21
Change 0 to 24:
SELECT
Quantity,
SEC_TO_TIME(Quantity*60*60) as TimeFormat,
CASE WHEN SEC_TO_TIME(Quantity*60*60) BETWEEN '01:00:00' AND '12:00:00' THEN 'AM' ELSE 'PM' END AS AMPM
FROM your_table
ORDER BY AMPM ASC, TimeFormat DESC
Quantity: column with your integer value
I would make use of a new table that maintains sorting for you. It could then be maintained by a non-programmer who may change their mind regarding the business rules. You could easily create a web form that maintains this table.
# table sorter
hour sortOrder
---- ----------
0 10
1 0
2 8
3 7
4 6
5 5
6 4
7 3
8 2
9 1
10 11
11 12
12 13
13 14
14 15
15 16
16 17
17 18
18 19
19 20
20 21
21 22
22 23
23 24
Your original table
# table things
data hour
---- -------
'something' 2
'something else' 4
Then join this table with SQL
select * from things t
join sorter s
on t.hour = s.hour
order by s.sortOrder