Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 077AE475EC8 for ; Tue, 14 May 2002 08:50:47 -0400 (EDT) Received: from temp.joelburton.com (unknown [209.190.238.101]) by postgresql.org (Postfix) with ESMTP id 620BD4758F2 for ; Tue, 14 May 2002 08:43:16 -0400 (EDT) Received: from joeltf0d13cyyp (unknown [209.190.238.100]) by temp.joelburton.com (Postfix) with SMTP id A080D2B981; Tue, 14 May 2002 08:48:23 -0400 (EDT) From: "Joel Burton" To: "Frank Morton" , Subject: Re: sort text field numerically Date: Tue, 14 May 2002 08:43:19 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal In-Reply-To: <020101c1fb42$3665f770$8455e5ce@edison> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200205/138 X-Sequence-Number: 7774 > -----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