Re: sort text field numerically

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Frank Morton
> Sent: Tuesday, May 14, 2002 8:24 AM
> To: pgsql-sql(at)postgresql(dot)org
> Subject: [SQL] sort text field numerically
>
>
> I have a table with a column called "weight" that
> is a text field, but in this case, contains float values,
> such as ".8" or "0.25". Might also be null.
>
> How can I sort them in numeric order instead of
> textual order? Thanks.

select * from floats_table order by text_float_field::float;

will work, as long as there are no non-float items in the table (if there
are, you could use an ORDER BY CASE ... to look for non-numeric characters
and sort these differently, and sort the rest by converting to floats as
::float)

HTH.

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant