custom sort in mysql

Question

The data below is a string column sorted in ascending order:

1/44/104/66
1/44/104/66/137
1/44/104/66/138
1/44/104/66/139
1/44/104/66/140
1/44/104/66/141
1/44/104/66/142
1/44/104/66/143
1/44/104/66/67
1/44/104/66/68
1/44/104/66/69

But I would like to sort it like this:

1/44/104/66
1/44/104/66/67
1/44/104/66/68
1/44/104/66/69
1/44/104/66/137
1/44/104/66/138
1/44/104/66/139
1/44/104/66/140
1/44/104/66/141
1/44/104/66/142
1/44/104/66/143

Are you just sorting by the last column? In plain english, how would you describe the sort? — What have you tried, Apr 16 '13 at 18:21
You might find some answers here regarding casting a field as an integer for numeric sorting: http://stackoverflow.com/questions/4686849/sorting-varchar-field-numerically-in-mysql — showdev, Apr 16 '13 at 18:23
where should the string `1/44/105/32/25` end up in your dataset? — didierc, Apr 16 '13 at 18:30
Refer http://stackoverflow.com/questions/5960620/convert-text-into-number-in-mysql-query — Slowcoder, Apr 16 '13 at 18:51

score 0 · Answer 1 · answered Apr 16 '13 at 18:21

0

For the data that you have, this should work:

order by length(col), col

However, this assumes that the only difference is in that last column.

answered Apr 16 '13 at 18:21

Gordon Linoff

1,242,037
58
646
786

What if string A is larger than string B (numerically) but string A is *shorter* than string B? PS: I (was) the downvoter but the user didn't give enough details for this answer to be incorrect, per se. Just thought I'd point that out – What have you tried Apr 16 '13 at 18:22
@Evan . . . My answer clearly states that it works for the data provided in the question, with the assumption that only the last field changes. Is there something about the answer you don't understand? – Gordon Linoff Apr 16 '13 at 18:27

score 0 · Answer 2 · answered Apr 16 '13 at 18:33

0

you could try something along the lines of:

select
    Name
from TableOne
ORDER BY Cast(Replace(Name, "/", "") as UNSIGNED)

answered Apr 16 '13 at 18:33

Chris Piazza

251
2
6

score 0 · Answer 3 · answered Apr 16 '13 at 18:33

SELECT * FROM myTable 
ORDER BY CAST(SUBSTRING_INDEX(DATA_COLUMN,"/",-1) AS UNSIGNED) ;

How it works :

1: In your original order by, the order was not correct since it was comparing lexographically for strings, whereas you wanted ordering based on integer value.

2.. In this solution, SUBSTRING_INDEX extracts the last value after / , and then casts it as an integer to give you the required order.

3.. You can extend the same solution to order by second last, third last numbers in your data pattern.

score 0 · Answer 4 · answered Apr 16 '13 at 20:01

Not really a beautiful query, but it should do what you need:

SELECT s
FROM
  yourtable
ORDER BY
  s + 0,
  CASE WHEN LENGTH(s)-LENGTH(REPLACE(s, '/',''))>0
       THEN SUBSTRING_INDEX(SUBSTRING_INDEX(s, '/', 2), '/', -1)+0
       ELSE 0 END,
  CASE WHEN LENGTH(s)-LENGTH(REPLACE(s, '/',''))>1
       THEN SUBSTRING_INDEX(SUBSTRING_INDEX(s, '/', 3), '/', -1)+0
       ELSE 0 END,
  CASE WHEN LENGTH(s)-LENGTH(REPLACE(s, '/',''))>2
       THEN SUBSTRING_INDEX(SUBSTRING_INDEX(s, '/', 4), '/', -1)+0
       ELSE 0 END,
  CASE WHEN LENGTH(s)-LENGTH(REPLACE(s, '/',''))>3
       THEN SUBSTRING_INDEX(SUBSTRING_INDEX(s, '/', 5), '/', -1)+0
       ELSE 0 END

Please see fiddle here.

custom sort in mysql

4 Answers4