Thread: 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
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
Anne Wainwright wrote on 14.09.2013 14:07: > 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? > What datatype is the shop_price column? Also please show us the full query.
what is the type of field？try order by shop_price::numeric.
jov
On Sep 14, 2013 8:06 PM, "Anne Wainwright" <anotheranne@fables.co.za> wrote:
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
Thanks for what I hoped would be an easy solution. But this fails at the onset of a check for the validity of the sql query. This is likely to do with the CRUD front end that I am using (Kexi). I'll try this out when I access pg directly. So onto the next reply. many thanks. Anne On Sat, Sep 14, 2013 at 08:19:01PM +0800, Jov wrote: > what is the type of field？try order by shop_price::numeric. > jov > On Sep 14, 2013 8:06 PM, "Anne Wainwright" <anotheranne@fables.co.za> wrote: > > > 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 > >
Anne Wainwright wrote > Thanks for what I hoped would be an easy solution. But this fails at > the onset of a check for the validity of the sql query. This is likely > to do with the CRUD front end that I am using (Kexi). I'll try this out > when I access pg directly. > > So onto the next reply. > > many thanks. > Anne > > > On Sat, Sep 14, 2013 at 08:19:01PM +0800, Jov wrote: >> what is the type of field？try order by shop_price::numeric. >> jov >> On Sep 14, 2013 8:06 PM, "Anne Wainwright" < > anotheranne@.co > > wrote: >> >> > 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 Two possibilities: 1) shop_price is defined as text, but numeric 2) your client is broken You have to tell us which one (possibly both) is true. If your truly issue "sort by shop_price" instead of "order by shop_price" you are not speaking SQL directly so any solutions are going to be client-specific and you should ask them. PostgreSQL will order this properly if provided a correct query and shop_price is numeric in nature. If you need to alter the field type you can use the "Alter Table" command; it is well documented. I will require a full table rewrite so depending on the table size it could take a while. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/sorting-of-a-price-field-tp5770849p5770957.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
>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? may be data type of shop_price is text so you can either type cast it in below way or alter table and change the data type as numeric(if it is not violating any other constraints). select * from table_name order by shop_price::numeric; ----- Thanks and Regards, Vinayak Pokale, NTT DATA OSS Center Pune, India -- View this message in context: http://postgresql.1045698.n5.nabble.com/sorting-of-a-price-field-tp5770849p5771033.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.