Mysql sort by user defined advanced

Question

Apologies if this question has already been answered, I've already done an extensive search but haven't come across an answer. Is it possible to do an alphabetical sort by with own defined.

Example items with cell name:

Sort By name ASC:

Brand1 Model1 Premium
Brand1 Model1 Standard
Brand1 Model1 Normal
Brand1 Model2 Normal
Brand1 Model2 Premium
Brand1 Model2 Standard
Brand1 Model3 Normal
Brand1 Model3 Premium
Brand1 Model3 Standard

But I want defined own order example:

Brand1 Model2 Premium <- model2 firt and best version
Brand1 Model2 Normal <- model2 next alphabetical order
Brand1 Model2 Standard <- model2 next alphabetical order
Brand1 Model1 Premium <- model2 next alphabetical by premium first
Brand1 Model1 Normal
Brand1 Model1 Standard
Brand1 Model3 Premium
Brand1 Model3 Normal
Brand1 Model3 Standard

I try used like as:

Case `name`
    when "Brand1 Model2 Premium" then 1
    when "Brand1 Model2 %" then 2
    when "Brand1 Model1 Premium" then 3
    when "Brand1 Model1 %" then 4
    when "Brand1 Model% Premium" then 5 <- next normal sorting only by 
    when "Brand1 Model%" then 6
    else 7
end,

Of course Brand, Model and version is one cell. Exactly it's name of product. I don't explode product name.

Ravinder Reddy · Accepted Answer · 2014-04-24 16:42:19Z

1

If the name value pattern is fixed as brandX modelY Type, then following solution should work.

Use ORDER BY FIELD on splitting the name into desired words order.

Example:

select name from (
  select name
   , @s1:=substring( name, locate( ' ', name )+1 ) s1 -- ModelY Premium/Normal/Standard
   , @s2:=substring_index( @s1, ' ', 1 ) s2           -- ModelY
   , @s3:=substring_index( @s1, ' ', -1 ) s3          -- Premium/Normal/Standard
  from table_name
) segregated_name_results
order by 
 field( s2, 'Model2', 'Model1', 'Model3' )
 , field( s3, 'Premium', 'Normal', 'Standard' )
;

+------------------------+
| name                   |
+------------------------+
| Brand1 Model2 Premium  |
| Brand1 Model2 Normal   |
| Brand1 Model2 Standard |
| Brand1 Model1 Premium  |
| Brand1 Model1 Normal   |
| Brand1 Model1 Standard |
| Brand1 Model3 Premium  |
| Brand1 Model3 Normal   |
| Brand1 Model3 Standard |
+------------------------+

Reference Values on Sub-query:

select name
 , @s1:=substring( name, locate( ' ', name )+1 ) s1
 , @s2:=substring_index( @s1, ' ', 1 ) s2
 , @s3:=substring_index( @s1, ' ', -1 ) s3
from brand_models

+------------------------+-----------------+--------+----------+
| name                   | s1              | s2     | s3       |
+------------------------+-----------------+--------+----------+
| Brand1 Model1 Premium  | Model1 Premium  | Model1 | Premium  |
| Brand1 Model1 Standard | Model1 Standard | Model1 | Standard |
| Brand1 Model1 Normal   | Model1 Normal   | Model1 | Normal   |
| Brand1 Model2 Normal   | Model2 Normal   | Model2 | Normal   |
| Brand1 Model2 Premium  | Model2 Premium  | Model2 | Premium  |
| Brand1 Model2 Standard | Model2 Standard | Model2 | Standard |
| Brand1 Model3 Normal   | Model3 Normal   | Model3 | Normal   |
| Brand1 Model3 Premium  | Model3 Premium  | Model3 | Premium  |
| Brand1 Model3 Standard | Model3 Standard | Model3 | Standard |
+------------------------+-----------------+--------+----------+

Demo @ MySQL 5.5.32 Fiddle

answered Apr 24, 2014 at 16:42

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Damian S Over a year ago

Tomorrow check your solution. Today, I am already too tired to adapt next version :)

Damian S Over a year ago

Exactly! Thanks this solution 2 with order by field desc is almost perfect. The rest of "exceptions" will pick up by php.

Gordon Linoff · Accepted Answer · 2014-04-24 15:25:59Z

0

You can do it like this:

order by substring_index(name, ' ', 2),
         (case when substring_index(name, ' ', -1) = 'Premium' then 1
               when substring_index(name, ' ', -1) = 'Standard' then 2
               when substring_index(name, ' ', -1) = 'Normal' then 3
          end)

This assumes that the special string ("premium" and so on) is at the end of the name and the first two words are sufficient for sorting the name column otherwise.

answered Apr 24, 2014 at 15:25

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Mysql sort by user defined advanced

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related