-1
qry = "SELECT * FROM courses ORDER BY price DESC "

here I have declared price as varchar for logical reasons, so in case of order it gives

output:

{
"id": 5,
"payment_type": "one time",
"price": "1000"
},
{
"id": 11,
"payment_type": "one time",   # issue here
"price": "10000" 
},
{
"id": 10,
"payment_type": "Lumpsum",     # issue here
"price": "12000"
},
{
"id": 2,
"payment_type": "one time",
"price": "3000"
},
{
"id": 13,
"payment_type": "one time",
"price": "3500"
},
{
"id": 12,
"payment_type": "one time",
"price": "4500"
},

.... and so on

Can I ordered this price without changing the datatype of this column?

SET : type of column "varchar" TRYING TO GET : ORDER BY price ASC Or DESC

Shadow
  • 33,525
  • 10
  • 51
  • 64
Rehan
  • 1
  • 3
    Why use varchar for price? – Kraigolas Mar 07 '23 at 15:12
  • 1
    You'll need to cast `price` to a number otherwise you will get lexicographical sort like you are seeing. `ORDER BY price::int DESC` will likely do the trick. The real issue is storing a number as a `varchar`. Your best off just fixing your table. – JNevill Mar 07 '23 at 15:12
  • 1
    @JNevill 's solution is what I would think to do as well, but if you're using varchar then probably there will be some elements that cannot be cast to a number (otherwise why would you use varchar in the first place). If you expect to use price like a number you'll likely have an easier time using the correct datatype. – Kraigolas Mar 07 '23 at 15:14

1 Answers1

0

You're trying to use ORDER BY on a string so it tries to sort lexically, one solution would be to cast the type: SELECT * FROM courses ORDER BY CAST('price' AS decimal) DESC;

Hope it works!

Lucas
  • 1
  • 1