2

I have a varchar column that I am currently sorting by using: ORDER BY (col_name+0)

This column contains both digits and non-digits, and the result of this sorting is this:

D3
D111
M123-M124
M136
4
9
10
25
37b
132
147-149
168b
168ca

This sorting is almost perfect for our application, but with one exception: we want the items that start with letters to display after those that start with numbers. This being the ideal result:

4
9
10
25
37b
132
147-149
168b
168ca
D3
D111
M123-M124
M136

I'm hoping this can be achieved in the select statement, rather than needing to loop through everything in code again after the select. Any ideas?

bassnoodle
  • 101
  • 1
  • 1
  • 5
  • 1
    http://stackoverflow.com/questions/11433852/order-by-sorting-characters-before-numbers – tbraun89 Jan 23 '13 at 18:34
  • i don't think this question is an exact duplicate, here rows that starts with an alphanumeric character go to the top because of the + 0, and they still need to be sorted... – fthiella Jan 25 '13 at 17:10

1 Answers1

1

You can use this:

ORDER BY
  col_name regexp "^[^0-9]",
  case when col_name regexp "^[0-9]" then col_name + 0
       else mid(col_name, 2, length(col_name )-1) + 0 end,
  col_name

this will put rows that begins with a digit at the top. If col_name begins with a digit, I'm sorting by it's numeric value, if not I'm sorting by the numeric value of the string beginning at the second character.

fthiella
  • 48,073
  • 15
  • 90
  • 106
  • Tried this. It does display the elements starting with letters after the numbers. However, it reverses the sorting on other non-numeric characters. For example, 168ca now displays before 168b. – bassnoodle Jan 23 '13 at 18:46
  • @bassnoodle I edited ORDER BY and now I'm ordering by `col_name` as well. Since both `168b+0` and `168ca+0` are evaluated to `168`, they can appear in any order, this should solve the problem – fthiella Jan 23 '13 at 19:18
  • The change you made works perfect with one exception... If the entries are: 7a, 7b, 20e, 20m. Then it sorts them as 7a, 7b, 20m, 20e (where we'd like e to come before m). – bassnoodle Jan 23 '13 at 19:21
  • @bassnoodle they should be sorted correctly, 7a, 7b, 20e, 20m, see this fiddle http://sqlfiddle.com/#!2/6da5e/1 – fthiella Jan 23 '13 at 19:25
  • If you go to this page [link]http://www.paloalbums.com/index.php?p=collections[/link] and select "Canada" from the search and click "Search". Then look toward the bottom of the results. I've applied your solution to the column that says Gibbons number and there are some unordered results in the Gibbons numbers that start with letters. Any idea? – bassnoodle Jan 26 '13 at 20:12
  • Is it possible that ordering by name first then gibbons number could alter the results? This is the query: SELECT * FROM table_name WHERE category_id = 63 AND display = 1 AND name like '%Canada%' ORDER BY name, gibbonsnum regexp "^[^0-9]", case when gibbonsnum regexp "^[0-9]" then gibbonsnum + 0 else mid(gibbonsnum, 2, length(gibbonsnum )-1) + 0 end – bassnoodle Jan 26 '13 at 20:18
  • @bassnoodle my answer is something like a "dirty" solution, I'm only considering columns that begins with a single alphanumeric character, if there is more than one it won't work (but this is easy to fix). Also, it's not possible to sort correctly rows that have more than one block, like "D1-D2", "D1-D3", "D1-D100" ... I think you need a custom function here... I'll edit my answer if I have another solution – fthiella Jan 28 '13 at 17:31
  • @bassnoodle pls see my answer to another question http://stackoverflow.com/questions/14584364/mysql-varchar-ordering/14593243#14593243 which is very similar to yours, you could use a custom function – fthiella Jan 29 '13 at 22:45