Re: ORDER BY question

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 somerows is defined, and modeling a query which
hassequential numbers(e.g. 1, 2, 3) to sort and SUBSTRINGs to divide the value into the threeparts. 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
whereid < 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
----------20010822133812001112121523
(6 rows)




Regards,
Masaru Sugawara