mySQL order by clause weird problem

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

select * from customers order开发者_Python百科 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.

---

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

---

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.

---

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.

---

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.

---

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.

---

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');