Sqlite, sort second column without messing the order in the first

user_8441087 0
Viewed 60

I have the following table:

15|flex        |2|low   |Not started
16|flex        |1|medium|Not started
17|flex        |2|medium|Not started
18|gridonly-med|1|low   |Not started
19|gridonly-med|2|low   |Not started
20|gridonly-med|4|low   |Not started
21|gridonly-med|1|medium|Not started
22|gridonly-med|2|medium|Not started
23|gridonly-med|4|medium|Not started

and what I need is to sort it(desc) by 3-rd column without messing column 2 and column 4.

Basically, I need to get this table:

15|flex        |2|low   |Not started
17|flex        |2|medium|Not started
16|flex        |1|medium|Not started
20|gridonly-med|4|low   |Not started
19|gridonly-med|2|low   |Not started
18|gridonly-med|1|low   |Not started
23|gridonly-med|4|medium|Not started
22|gridonly-med|2|medium|Not started
21|gridonly-med|1|medium|Not started

I have tried:

ORDER BY column4 DESC, column1 ASC

but it didn't work.

I get:

23|gridonly-med|4|medium|Not started
20|gridonly-med|4|low   |Not started
22|gridonly-med|2|medium|Not started
19|gridonly-med|2|low   |Not started
17|flex        |2|medium|Not started
21|gridonly-med|1|medium|Not started
18|gridonly-med|1|low   |Not started
16|flex        |1|medium|Not started

If someone points out any clause or any other thing which I could use to solve my issue I would really appreciate any help, thank you.

sql-order-by sqlite database
1 Answers

You must sort first by col2, then by col4 and finally by col3 descending:

ORDER BY col2, col4, col3 DESC

Note that I use the fact that 'flex' is sorted alphabetically before 'gridonly-med' and 'low' is sorted alphabetically before medium, so if there were different values the code would not work.

For certain results you should use conditional sorting:

ORDER BY col2 = 'flex' DESC, 
         col4 = 'low' DESC, 
         col3 DESC

or:

ORDER BY CASE col2 WHEN 'flex' THEN 1 ELSE 2 END, 
         CASE col4 WHEN 'low' THEN 1 ELSE 2 END, 
         col3 DESC

and you can extend the branches of the CASE expressions to include more values.

See the demo.

user_104988280
Related