Re: sort text field numerically

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Frank Morton
> Sent: Tuesday, May 14, 2002 8:24 AM
> To: pgsql-sql@postgresql.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@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant