CODEWITHSUNDEEP
mysqlsqldatabase
Pramod
Pramod

Reputation: 1

Order by clause confusion

Below are the contents of sid column in a table ( where "S05201215" is a fixed string and remaining part of the Sring is a sequence of numbers)

S052012151 
S052012152 
S052012153 
S052012154
S052012155
S052012156

I want to sort the the remaining part of the string (i.e. the numbers that i've appended to the string S05201215 in descending order .. what modifications should i do to the below query to get desired output?

SELECT `sid` FROM `mytable` order by SUBSTRING(`sid`,10,length(`sid`))

Upvotes: 0

Views: 116

Answers (4)

bobwienholt
bobwienholt

Reputation: 17610

To sort in descending order, just add "DESC" to the end of your ORDER BY:

SELECT `sid`
FROM `mytable`
ORDER BY SUBSTRING(`sid`, 10, length(`sid`)) DESC

However, if the values of sid are of different lengths, you are probably going to want to cast the values to a numeric type before sorting:

SELECT `sid`
FROM `mytable`
ORDER BY CAST(SUBSTRING(`sid`, 10, length(`sid`)) AS SIGNED) DESC

Upvotes: 1

jhenderson2099
jhenderson2099

Reputation: 964

Have you tried:

SELECT `sid` FROM `mytable` order by SUBSTRING(`sid`,10,length(`sid`)) DESC

Upvotes: 2

Kerrek SB
Kerrek SB

Reputation: 477030

I don't quite understand your data, but maybe something like this:

  SELECT SUBSTRING(sid, 0, 10) AS prefix, SUBSTRING(sid, 11) AS suffix
    FROM mytable
ORDER BY suffix

It look suspiciously like you're trying to implement your own little database inside the database, though, which is something you should avoid at all cost - let the database do what it's good at and normalize your model.

Upvotes: 0

Itay Moav -Malimovka
Itay Moav -Malimovka

Reputation: 53607

Assuming (assumptions are bad) the string remains the same length ALL THE TIME, just do a simple order by sid

Upvotes: 1

Related Questions