CODEWITHSUNDEEP
sqlmysqlsql-order-by
YD8877
YD8877

Reputation: 10780

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 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

Upvotes: 0

Views: 292

Answers (6)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

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

Upvotes: 0

Yuriy
Yuriy

Reputation: 176

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.

Upvotes: 0

Chandu
Chandu

Reputation: 82893

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

Upvotes: 4

manishKungwani
manishKungwani

Reputation: 925

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.

Upvotes: 0

Macy Abbey
Macy Abbey

Reputation: 3887

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.

Upvotes: 1

Randy
Randy

Reputation: 16677

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.

Upvotes: 1

Related Questions