MySQL - sort by certain last string character

Question

I'm trying to sort by a certain character on a string, for example,

before:

+----+---------+
| id |  name   |
+----+---------+
| 1  | red     |
| 2  | red-a   |
| 3  | red-xy  |
| 4  | blue    |
| 5  | blue-a  |
| 6  | blue-xy |
+----+---------+

after:

+----+---------+
| id |  name   |
+----+---------+
| 4  | blue    |
| 1  | red     |
| 5  | blue-a  |
| 2  | red-a   |
| 6  | blue-xy |
| 3  | red-xy  |
+----+---------+

are there any ways to categorize based on -a or -xy using ORDER BY

Thank you in advance.

Sebas · Accepted Answer · 2015-07-23 17:21:19Z

1

SELECT 
  CASE 
    WHEN RIGHT(`name`,LENGTH(`name`)-INSTR(`name`,'-')) = `name` THEN ''
    ELSE RIGHT(`name`,LENGTH(`name`)-INSTR(`name`,'-')) 
  END AS `suffix`,
  `name`
FROM 
  `table1`
ORDER BY
  `suffix`, `name`

If no suffix is found, it will put the record in the first row set. Caveat: the first dash is used to separate the word from the prefix.

answered Jul 23, 2015 at 17:21

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

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

Comments

mcr · Accepted Answer · 2015-07-23 17:35:56Z

1

This will do what you're looking for. Wouldn't like to promise great performance if you had a lot of rows though:

select id, name from 
(
  select id, 
         name, 
         if (substring_index(name,'-', -1) = name, '', substring_index(name,'-', -1)) as grouping
  from Table1
  order by grouping, name
) as subTable

SQLFiddle here

[EDIT] Actually, that can be simplified to a single select with :

select id, 
       name
from Table1
order by  if (substring_index(name,'-', -1) = name, '', substring_index(name,'-', -1)), name

edited Jul 23, 2015 at 17:35

answered Jul 23, 2015 at 17:29

mcr

7825 silver badges19 bronze badges

1 Comment

brianlasta Over a year ago

Thank you, this is very helpful! :)

Raymond Nijland · Accepted Answer · 2015-07-23 18:57:33Z

1

These queries are more readable and this is probably the easiest way to sort on a suffix

SELECT 
 *
 , IF (LOCATE('-', name) = 0
     , 0
     , LENGTH(SUBSTRING_INDEX(name, '-', -1))   
   )
     suffix_length

FROM 
 Table1

ORDER BY 
 suffix_length
;



SELECT 
 *

FROM 
 Table1

ORDER BY 
  IF (LOCATE('-', name) = 0
     , 0
     , LENGTH(SUBSTRING_INDEX(name, '-', -1))   
   )
;

See demo http://sqlfiddle.com/#!9/92b63/41

answered Jul 23, 2015 at 18:57

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

Collectives™ on Stack Overflow

MySQL - sort by certain last string character

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related