Non-standard sort order

Question

I have the following data in an SQL column. Column type is String. I would prefer to sort this data when I query it from the DB, but I suppose it could be organized with some Ruby magic after the SQL query is complete. There are other columns being pulled out, so this is just one of the columns that needs to be sorted.

Expenses
$3500/MONTH
$1,000.00
STANDARD CONTRACTOR
$5,000.00

The data cannot change and must be stored in the same column (the example above just test data).

I would like to organize the data by increasing number and then the rest (string) values. The expected result should be:

$1,000.00
$5,000.00
$3500/MONTH
STANDARD CONTRACTOR

or

$1,000.00
$5,000.00
STANDARD CONTRACTOR
$3500/MONTH

I think that you are looking for a "Natural sort". If so this [has been done before](http://stackoverflow.com/q/34518/2509). — dmckee --- ex-moderator kitten, Nov 18 '12 at 23:59
@dmckee, the problem is no other questions had sample data like mine. I searched for a while. Most was of the type: `A1,A12,A2`, etc. — Kevin Zych, Nov 19 '12 at 00:23

score 1 · Accepted Answer · answered Nov 19 '12 at 01:06

Option 1 (fiddle here):

select expenses from table1
order by
  replace(replace(expenses, "$", ""), ",", "") regexp "[0-9.]*" desc,
  case when replace(replace(expenses, "$", ""), ",", "") regexp "[0-9.]*"
    then cast(replace(replace(expenses, "$", ""), ",", "") as real)
    else 0
  end

Option 2 (fiddle here):

select expenses from (
    select expenses,
      replace(replace(expenses, "$", ""), ",", "") expensesNormalized
    from table1
) s
order by
  expensesNormalized regexp "[0-9.]*" desc,
  case when expensesNormalized regexp "[0-9.]*"
    then cast(expensesNormalized as real)
    else 0
  end

Go for whichever runs faster.

I ended up using Option 1 but had to massage the answer slightly to make it work for my specific (actual) data. +1 for leading me in the right direction and the fiddle examples. — Kevin Zych, Nov 19 '12 at 16:55

Gordon Linoff · Answer 2 · 2012-11-19T14:41:52.540

In SQL Server, you can do something like the following:

order by (case when isnumeric(col) = 1 then 1 else 0 end) desc,
         (case when isnumeric(col) = 1 then cast(col as money) end),
         col

For other databases, you can use regular expression matching rather than isnumeric to determine which are numbers.

Since you are using SQLite and I seem to be able to assume that all the numbers start with a '$', then something like this almost works:

order by (case when substr(col, 1, 1) = '$' then 1 else 0 end) desc,
         (case when substr(col, 1, 1) = '$' then cast(col as numeric) end),
         col

The problem is that "$3,500/month" is treated as a number. You can fix this with a regexp, if you have that included. Or, if the "/" defines these cases, you can use something specific like:

order by (case when substr(col, 1, 1) = '$' and col not like '%/%' then 1 else 0 end) desc,
         (case when substr(col, 1, 1) = '$' and col not like '%/%' then cast(col as numeric) end),
         col

score 0 · Answer 3 · edited May 23 '17 at 12:04

If the existing answers work in SQLlite, then perhaps they'll work for you. Because the data is pretty ... messy ... I would tend to try to encapsulate the ugly part of this in a single method that returns a sorted array.

If you have a small number of rows like this (e.g. less than 500) then the cleaner way to sort would be to include Comparable and write a custom comparator, as nicely documented in this SO answer.

Non-standard sort order

3 Answers3