Date: Thu, 22 Nov 2001 00:11:48 +0900
From: Masaru Sugawara <rk73@echna.ne.jp>
To: pgsql-sql@postgresql.org
Subject: Re: ORDER BY question
In-Reply-To: <a05010403b819f5e7857d@[152.3.11.193]>
References: <a05010403b819f5e7857d@[152.3.11.193]>
Errors-To: <rk73@echna.or.jp>
Message-Id: <20011121221141.B82B.RK73@echna.ne.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit

On Thu, 15 Nov 2001 18:02:04 -0500
Charles Hauser <chauser@acpub.duke.edu> wrote:


> Hope this is the correct forum - if not sorry.
> 
> I have a query which returns a value of the form: '20010822.1338.1'.
> 
> I would like to order the results, sorting :
> 
> 		1st: 20010822
> 		2nd: 1338
> 		3rd: 1
> 
> 
> I can do this in perl, but have not found a way to do this using ORDER BY.


 Hi,
 
 I'm supposing, for the sake of convenience, that a table including some
 rows is defined, and modeling a query which has sequential numbers
 (e.g. 1, 2, 3) to sort and SUBSTRINGs to divide the value into the three
 parts. If your platform is PG, the query might goes well. 
 BTW, a part of "select * from tbl where id < 3" depends on your query.
 

create table tbl (id int4, tm text);
insert into tbl values(1, '20010822.1338.1');
insert into tbl values(2, '20011121.2152.3');
insert into tbl values(3, '20011222.1338.1');

  
-- on v7.1.2
select t.dt
   from (select t1.id, 1 as rank, substring(t1.tm from  1 for 8) as dt
             from (select * from tbl where id < 3) as t1
         union all
         select t2.id, 2        , substring(t2.tm from 10 for 4)
             from (select * from tbl where id < 3) as t2
         union all
         select t3.id, 3        , substring(t3.tm from 15 for 1)
             from (select * from tbl where id < 3) as t3
         order by id, rank
         ) as t


    dt
----------
 20010822
 1338
 1
 20011121
 2152
 3
(6 rows)


Regards,
Masaru Sugawara