simple order by clause not working with inner join

this query is simply not ordering results the way i want it to:

$sql = mysql_query("select s.*, p.label, p.number
                    from `sales` as s 
                    开发者_Python百科left join `pumps` as p on (p.id = s.id_pump)
                    where s.the_date >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))
                    order by CAST(s.amount_sale AS int) desc
                    limit 1") or die(mysql_error());

i am getting a mysql error.

Try casting the VARCHAR to a signed integer:

$sql = mysql_query("select s.*, p.label, p.number
                    from `sales` as s 
                    left join `pumps` as p on (p.id = s.id_pump)
                    where s.the_date >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))
                    order by CAST(s.amount_sale AS SIGNED) desc
                    limit 1");