CODEWITHSUNDEEP
mysql
Nahuel
Nahuel

Reputation: 3585

MYSQL - ORDER BY Numeric column with parenthesis

I have an old database with a list of books. Each book has a volume number. The volumes can be part 1 and part 2. So, I have books with these volume numbers:

1
2
3
...
29 (1)
29 (2)
30 (1)
30 (2)

I need to select all volumes in DESC order, ordering by the volume number column. Originally I did this dynamic casting:

SELECT * FROM volumenes ORDER BY volumen_numero * 1 DESC

Which worked great until it reached 30! For some reason, it now returns this:

30 (1)
30 (2)
29 (2)
29 (1)
... all sorted correctly from this point on

I tried replacing the parenthesis from the column value, like so:

ORDER BY CAST(replace(replace(volumen_numero,")",""), "(", "") as unsigned)

But I get the exact same result.

What I'm looking for is this order:

30 (2)
30 (1)
29 (2)
29 (1)
...

Here you have a working SQL Fiddle

Upvotes: 1

Views: 274

Answers (2)

Lajos Veres
Lajos Veres

Reputation: 13725

You should debug in your select what happens. (Add the order expression to the select and verify it.)

I guess the space disturbs the integer conversion. (Probably the second part will be truncated.) If this assumption is true, you can fix it easily:

ORDER BY CAST(replace(replace(replace(volumen_numero,")",""), "(", "")," ","") as unsigned)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270523

The problem is that you are only sorting by the first number. Here is a more explicit approach:

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

The above will work as long as the volume number is one digit. You can handle longer volume numbers with:

order by substring_index(volumen_numero, ' ', 1) + 0,
         length(substring_index(volumen_numero, ' ', -1)),
         substring_index(volumen_numero, ' ', -1)

Or, if you prefer being cryptic and shorter:

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

Upvotes: 1

Related Questions