CODEWITHSUNDEEP
sqlmysqlsorting
gustyaquino
gustyaquino

Reputation: 767

Quick SQL question for sorting

I have this ordering sql at the end of my sql statement:

ORDER BY CASE WHEN LOWER(LEFT(E.name, 1)) BETWEEN 'a' AND 'z' 
              THEN 0 ELSE 1 END, E.name ASC

that orders the result this way: names starting with letters first and numbers later.

How can I add this: at first, names with a preference set on true, then all what I already have. ie: E.preference='true' will goes on the top.

Upvotes: 0

Views: 268

Answers (2)

codingbadger
codingbadger

Reputation: 43984

Can't you just add the preference sort before your existing sort?

ORDER BY Case When e.Preference = 'true' then 0 else 1 end,
CASE WHEN LOWER(LEFT(E.name, 1)) BETWEEN \'a\' AND \'z\' THEN 0 ELSE 1 END, E.name ASC

Edit

Or if you want to have all preference = true first in any order then sort using your existing method then I think you could use:

Order By Case When e.Preference = 'true' Then 0  
         Else
              CASE 
                  WHEN LOWER(LEFT(E.name, 1)) BETWEEN \'a\' AND \'z\' THEN 1 
                  ELSE 2 END
         End, e.name asc

Edit 2

Taking this one step further you can have all preference = true first then sort these using your current method, then sort all the preference=false 

Order By Case When e.Preference = 'true' Then 
                  CASE 
                  WHEN LOWER(LEFT(E.name, 1)) BETWEEN \'a\' AND \'z\' THEN 0 
                  ELSE 1 END  
         Else
              CASE 
                  WHEN LOWER(LEFT(E.name, 1)) BETWEEN \'a\' AND \'z\' THEN 2 
                  ELSE 3 END
         End, e.name asc

I'm not sure how this would affect the performance of the query though.

Upvotes: 2

Chris Cameron-Mills
Chris Cameron-Mills

Reputation: 4657

You can list multiple ORDER BY columns. So you could do ORDER BY e.preference, CASE WHEN LOWER(LEFT(E.name, 1)) BETWEEN \'a\' AND \'z\' THEN 0 ELSE 1 END, E.name ASC and if your non-preferred rows had NULL as preference then you'll get all preferred first (ordered by your existing criteria within that group) then all non-preferred (ordered by your existing criteria within that group)

Upvotes: 0

Related Questions