2

In mysql using cast, I wrote the query like

Accnno - varchar

SELECT * FROM books where category = 'Book' ORDER BY CAST(Accnno AS int) DESC LIMIT 0,10

The above query is working fine in the localhost, But is not working in the server, It shows error like

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int) DESC LIMIT 0,10' at line 1

Mukhila Asokan
  • 641
  • 3
  • 11
  • 29
  • Are you really sure it works in the localhost? https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=0057d2d4bb46fa5b5cdb504ffa4e348a doesn't accept it. – jarlh Apr 27 '21 at 06:58
  • 1
    It works in localhost. Instead of int, I used decimal, Now its works in server SELECT * FROM `books` where `category` = 'Book' ORDER BY CAST(`Accnno` AS DECIMAL) DESC LIMIT 0,10 – Mukhila Asokan Apr 27 '21 at 07:00
  • 2
    Yep, the matter is explained here: https://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql – Thorsten Kettner Apr 27 '21 at 07:02
  • INT is not legal datatype for CAST function in MySQL. Use UNSIGNED or SIGNED. Or use implicit convertion `ORDER BY 0 + Accnno` (will fail if first non-space char is not a digit). – Akina Apr 27 '21 at 08:16
  • Fix your database design and don't store numbers as strings to begin with. Then you can get rid of the cast completely –  Apr 27 '21 at 11:53

1 Answers1

0

I would suggest using implicit conversion:

SELECT b.*
FROM books b
WHERE b.category = 'Book'
ORDER BY (Accnno + 0) DESC
LIMIT 0, 10;

This will not generate an error. If Accnno does not start with leading zeros and has no decimal places (both of which seem likely), you can also just use string functions:

ORDER BY LENGTH(accnno) DESC, accnno DESC
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786