CODEWITHSUNDEEP
mysqlsql
ofo
ofo

Reputation: 1

SQL sort by number in column

 id|  name    |   created_at
    —-----------------------------
  1 | name 1  | 2017-05-20 
  2 | name 2  | 2017-05-22 
  3 | name 66  | 2017-05-24 
  4 | name 44  | 2017-05-25

i have a table Orders

I have to sort it by number in name column

like 

id|  name    |   created_at
        —-----------------------------
      1 | name 66  | 2017-05-20 
      2 | name 44  | 2017-05-22 
      3 | name 2  | 2017-05-24 
      4 | name 1  | 2017-05-25

I have tried SELECT * FROM Orders ORDER BY name DESC; but no luck;

how i can do it?

Upvotes: 0

Views: 59

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269453

If 'name' is not the same on each row, then you can do:

order by substring_index(name, ' ', 1),
         substring_index(name, ' ', -1) + 0

This will work if you have values such as:

a 1
a 2
a 10
b 1
b 100

(and of course if all start with "name" as well)

Upvotes: 0

kiran gadhe
kiran gadhe

Reputation: 743

Following query extracts number in name column with substring function and then order it accordingly. 

SELECT *
FROM Orders
ORDER BY CAST(SUBSTRING(name, length('name')+2 , 3) AS UNSIGNED) DESC

Upvotes: 0

Barry Piccinni
Barry Piccinni

Reputation: 1791

There is a simple trick to do this, that is to order by length first, then by name. Example:

SELECT *
FROM Orders
ORDER BY LENGTH(name) DESC, name DESC

Here's an example of this in action: SQL Fiddle

Edit: Please note, his will only work if your string is consistent as it is in your example data.

Upvotes: 0

Nick
Nick

Reputation: 147146

If all the names are in the format name, space, number you can use this query. The SUBSTRING_INDEX extracts the characters from the last space to the end and they are then CAST as an unsigned integer, which allows them to be sorted.

SELECT *
FROM Orders
ORDER BY CAST(SUBSTRING_INDEX(name, ' ', -1) AS UNSIGNED) DESC

Output:

id  name    created_at
3   name 66 2017-05-24
4   name 44 2017-05-25
2   name 2  2017-05-22
1   name 1  2017-05-20

Upvotes: 1

Related Questions