pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sandis Jerics <sandis@mediaparks.lv>
To: pgsql-sql@postgresql.org
Subject: sorting the text values as integers
Date: Fri, 10 Nov 2000 13:55:18 +0200
Message-ID: <3580.001110@mediaparks.lv> (raw)

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





Message-ID: <3580.001110@mediaparks.lv>
Permalink:  ../3580.001110@mediaparks.lv/
Also on:    postgresql.org/message-id/3580.001110@mediaparks.lv

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: sandis@mediaparks.lv
  Subject: Re: sorting the text values as integers
  In-Reply-To: <3580.001110@mediaparks.lv>
  References: <3580.001110@mediaparks.lv>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox