MySQL sectioned ordering

Question

I have a data set like this:

boolean name    value
0       Text10  20
1       Text1   8
0       Text4   46
1       Text9   84
1       Text5   66
0       Text2   35
0       Text9   2
1       Text6   55

Ordering by the boolean column will split the data in two sections that I want to order according to a different parameter each: The ones with boolean = 1 are ordered by value, and the rest are ordered by name, like this:

boolean name    value
1       Text1   8     # --> 1s are ordered by value
1       Text6   55
1       Text5   66
1       Text9   84
0       Text2   35    # --> 0s are ordered by name
0       Text4   46
0       Text9   2
0       Text10  20

Note: We need this to work in MySQL 4.1.11. =D

4.1.11 OMG! So sorry for you man.... – Ray Sep 21 '12 at 19:45 — Ray, Sep 21 '12 at 19:45
Thanks @Ray, but I'm sorry enough. =p – vmassuchetto Sep 21 '12 at 19:45 — vmassuchetto, Sep 21 '12 at 19:45

Saic Siquot · Accepted Answer · 2012-09-24T00:35:57.237

9

Notice that it can be done with multiple parts of order by that are 'activated' as needed.
You can check this sqlfiddle

select * from yourtable
order by 
  boolean desc, 
  case when boolean = 0 then value else null end, -- ´else null´ is redundant
  case when boolean = 1 then name  else null end  -- but is here to clarify

edited Sep 24 '12 at 00:35

answered Sep 21 '12 at 19:59

Saic Siquot

6,513
5
34
56

Took me some days to get back here, @bobwienholt. =D – vmassuchetto Sep 24 '12 at 11:03

MySQL sectioned ordering

1 Answers1