Re: sorting of a price field

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(at)(dot)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.