Re: ORDER BY question

On Thu, 15 Nov 2001 18:02:04 -0500
Charles Hauser <chauser(at)acpub(dot)duke(dot)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