mySQL order by clause weird problem

Question

I am trying to order the data in the table by using the following query :

select * from customers order by CUST desc

All the entries are being ordered properly except : CUST.10 How do i make it order properly so that CUST.10 shows at the top followed by CUST.9, CUST.8 and so on.

weird mysql ordering

Is it always going to be literal "CUST"? Or may you have CUST.1, CUST.10, BOB.1, BOB.2 etc — RichardTheKiwi, Jan 28 '11 at 00:31
See the [similar question](http://stackoverflow.com/questions/34509/natural-human-alpha-numeric-sort-in-microsoft-sql-2005) for SQL Server 2005, with the nice `len(CUST),CUST` proposal, or the use of C# function to build the values for `ORDER BY` (which does not help with the performance). — pascal, Jan 28 '11 at 03:44

score 4 · Accepted Answer · answered Jan 28 '11 at 00:09

4

Try this:

SELECT * 
  FROM customers 
 ORDER BY CAST(SUBSTRING_INDEX(CUST, '.', -1) AS SIGNED) DESC

Working Example:

SELECT * FROM
(
SELECT 'CUST.10' CUST 
UNION
SELECT 'CUST.9' CUST 
UNION
SELECT 'CUST.1' CUST 
) A ORDER BY CAST(SUBSTRING_INDEX(CUST, '.', -1) AS SIGNED) DESC

answered Jan 28 '11 at 00:09

Chandu

81,493
19
133
134

I wish there was something similar to Oracles RegExp_Replace in MySQL. – Chandu Jan 28 '11 at 00:16

score 1 · Answer 2 · answered Jan 27 '11 at 23:57

1

this is sorting based on the string value - not the number. if you can split the number off to a numeric only value - then you can sort on that instead.

answered Jan 27 '11 at 23:57

Randy

16,480
1
37
55

score 1 · Answer 3 · answered Jan 27 '11 at 23:57

1

Consider putting the number you have appended to the Cust string into an integer column. Right now SQL is doing an alphanumeric sort on the varchar column CUST. This sorts with alphanumeric precedence at each character index. If you sort by an integer column, you will get what you want.

answered Jan 27 '11 at 23:57

Macy Abbey

3,877
1
20
30

the current system and the backend logic does not permit moving the integer to a different column because massive changes will need to be done in the backend logic. – YD8877 Jan 28 '11 at 00:07
You can do what Cybernate has below, however, you're adding runtime overhead that does not need to be there. – Macy Abbey Jan 28 '11 at 00:14

score 0 · Answer 4 · answered Jan 27 '11 at 23:59

With this Data type, you cannot, the natural ordering of strings (Assuming the data type is string) results in 10 being lower than 2 ... because it compares individual characters, starting from the first character. The first non-equal comparison is taken as the solution.

To solve the problem, you will either have to use some other data field or make this a number field.

Yuriy · Answer 5 · 2011-01-28T00:35:21.747

0

Not only it is problematic to predictably sort on string values but also it is straight inefficient. You should really consider modifying your schema and creating an integer column for customer id. It may also be that you already have this column somewhere else as a key, then definitely go ahead and sort by that column.

edited Jan 28 '11 at 00:35

answered Jan 28 '11 at 00:01

Yuriy

176
1
8

score 0 · Answer 6 · answered Jan 28 '11 at 00:36

You need to split the name into the name and number portions.
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

SELECT *
FROM CUSTTEST
ORDER BY
    SUBSTRING_INDEX(CUST,'.',1),
    1*SUBSTRING_INDEX(CUST,'.',-1) DESC

Using sample

create table CUSTTEST (CUST varchar(20));
insert CUSTTEST VALUES
 ('CUST.10'),('CUST.3'),('CUST.9'),('CUST.1'),
 ('BOB.11'),('BOB.13'),('BOB.2'),('BOB.5'),
 ('CYBERKIWI.11'),('CYBERKIWI.1');

mySQL order by clause weird problem

6 Answers6