CODEWITHSUNDEEP
phpsqlsortingtypes
Osny Netto
Osny Netto

Reputation: 572

Sorting by Price with SQL data type VARCHAR

I´m sorting by Price, look:

  SELECT Price 
    FROM re2_listings 
ORDER BY Price asc

Result:

1.200.000,00
1.500.000,00
200,00
3.000,00

...but the correct way is:

200,00
3.000,00
1.200.000,00
1.500.000,00

Understand? How to do this?

Upvotes: 1

Views: 7851

Answers (4)

OMG Ponies
OMG Ponies

Reputation: 332531

MySQL or SQL Server supports:

  SELECT t.price
    FROM (SELECT r.price
                 CAST(r.price AS DECIMAL(15,2)) 'dprice'
            FROM RE2_LISTING r) t
ORDER BY t.dprice

I casted the varchar column to the appropriate data type, and used the alias dprice so I could reference it for sorting. If you are sorting in ascending fashion, you don't need to specify ASC if you don't want to - it's the default.

Upvotes: 0

Jonathan Fingland
Jonathan Fingland

Reputation: 57167

first, it helps to know your SQL variant to answer your question.

If you still have the ability to control the schema, you are much better off using a numeric data type (e.g. int or float) for the column and formatting it as a price when it is displayed.

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50970

Obviously, VARCHAR is not the best datatype for the storage of numerical amounts. If you must use it, you need to CAST the price column to an appropriate numeric type in the SELECT and ORDER BY parts of your statement, or create a view containing the CAST and SELECT from the view.

The datatype to CAST to, and the syntax for CASTing are product-dependent.

Upvotes: 3

Lukasz Lysik
Lukasz Lysik

Reputation: 10610

$q1 = "select Price from re2_listings order by CAST(Price AS Float)  asc";

Upvotes: 1

Related Questions