The shop_price column is probably defined as a text instead of a numeric. You can
convert the field to a number and then sort _however_ if you have any non-numeric data
in the field you may get a conversion error. Below is what the query might look like. I was
going to try and suggest a nicer function than to_number(...) that would handle bad data
but when I googled for it I found that you have to use regular expressions or write your
own function. Writing your own six or seven line function is very workable but it would
be cool to have already built in functions to handle it. If you need to handle bad data
you might try googling for "postgresql safely convert to number".
SELECT to_number(shop_price) AS ShopPrice
FROM YourTableThatIsNotDefinedWellAsNumberFieldsShouldBeNumeric
ORDER BY to_number(shop_price)
From: Anne Wainwright <anotheranne@fables.co.za>
To: pgsql-novice <pgsql-novice@postgresql.org>
Sent: Saturday, September 14, 2013 5:07 AM
Subject: [NOVICE] sorting of a price field
Hi,
I run a fairly simple query to show current records of stock using the
phrase 'sort by shop_price'.
Surprise (but really no surprise) this is sorted in ascii order rather
than shop_price order. So 110.00 comes before 20.00
I can't see any reference to changing this. What can I do, is this
perhaps to do with the field type, can it be changed?
Thanks
Anne
--
Sent via pgsql-novice mailing list (
pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice