Re: sorting of a price field

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(at)fables(dot)co(dot)za>
To: pgsql-novice <pgsql-novice(at)postgresql(dot)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