Search code examples
sqlsybase

ORDER BY desc in Sybase

I have a table that has : 

nb  | label 
60  | from 2 and less
25  | from 3 to 16
15  | from 17 to 100

I try to get the descending order so I use the query : 

select * from table order by label desc;

But I don't get the right order, instead I have the following :

[ { nb: 25, label: 'from 3 to 16' },
  { nb: 60, label: 'from 2 and less' },
  { nb: 15, label: 'from 17 to 100' } ]

Does it consider 17 as 1 ? How can I get the right order?

Thank you for your help

Solution

  • This is a pain. The easiest is if you have another column that has the same ordering. But assuming the second word is an integer that does not start with 0 and the first word is "from":

    order by patindex('%[0-9] %', label) desc,
         left(label, patindex('%[0-9] %', label)) desc

    This essentially finds the length of the first number and uses that for the first key in the order by. Then it orders by the first number.