From: Masaru Sugawara <rk73@echna.ne.jp>
To: pgsql-sql@postgresql.org
Subject: Re: ORDER BY question
Date: Thu, 22 Nov 2001 00:11:48 +0900
Message-ID: <20011121221141.B82B.RK73@echna.ne.jp> (raw)
In-Reply-To: <a05010403b819f5e7857d@[152.3.11.193]>
References: <a05010403b819f5e7857d@[152.3.11.193]>
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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rk73@echna.ne.jp
Subject: Re: ORDER BY question
In-Reply-To: <20011121221141.B82B.RK73@echna.ne.jp>
* 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