Received: from vmx.mediaparks.lv (vmx.mediaparks.lv [62.85.15.11]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBKNxX637556 for ; Wed, 20 Dec 2000 18:59:34 -0500 (EST) (envelope-from sandis@mediaparks.lv) Received: (from mail@localhost) by vmx.mediaparks.lv (8.9.3/8.9.3) id BAA11156 for pgsql-sql@postgresql.org; Thu, 21 Dec 2000 01:59:26 +0200 (EET) (envelope-from sandis@mediaparks.lv) X-Authentication-Warning: vmx.mediaparks.lv: mail set sender to sandis@mediaparks.lv using -f Received: from 195.2.123.209 by 62.85.15.11 id Ea57s2jZ ; 21 Dec 2000 01:59:20 EET Date: Fri, 10 Nov 2000 13:55:18 +0200 From: Sandis Jerics X-Mailer: The Bat! (v1.41) Organization: Mediaparks X-Priority: 3 (Normal) Message-ID: <3580.001110@mediaparks.lv> To: pgsql-sql@postgresql.org Subject: sorting the text values as integers Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200012/239 X-Sequence-Number: 576 Hi, i have a table with some text fields filled with a data like 100,23 235,12 500 200 the same fields somethimes contains the values like 100x100x25 125x125x50 200x80x90 and so on. the client requires that rows are sorted in ascending order for the case there are a float values, i do: SELECT ... ORDER BY float4(field) for the case there a text values, i do: SELECT ... ORDER BY int2(substring(field from 1 for position('x' in field)-1)); so i can sort them ascendingly at least by the first integer (before 'x' char). otherwise (simply "ORDER BY field") they were sorted as text values - 100x100x30, 10x10x10, 400x400x30, 40x40x20, ... now it sorted as i need - 10x10x10, 40x40x20, 100x100x30, 400x400x30 ... it's almost fine, but... now i need to combine that 2 cases, so i try (the field called m1): SELECT ... ORDER BY (CASE WHEN position('x' in m1)>1 THEN int2(substring(m1 from 1 for position('x' in m1)-1)) ELSE float4(m1) END) i never used CASE WHEN ... THEN ... ELSE ... END construct before, & assume the above is errorneus by default. --:)-- Best regards, Sandis