Postgres Pro
Downloads
Home   >   mailing lists

Thread: ORDER BY question

ORDER BY question

From
Charles Hauser
Date:
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.

I am VERY new to DB's; biologist trying to do informatics.

Thanks.
-- 
Chuck

Re: ORDER BY question

From
Masaru Sugawara
Date:
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

Re: ORDER BY question

From
"Andrew G. Hammond"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 2001 November 15 06:02 pm, Charles Hauser 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.

It looks like you're encoding date and then two numeric data types into a 
single text string.  Why?  Not only is it inefficient from a storage 
perspective, but it will limit the flexibility (and value) of your data in 
the future.  Solution?  Redesign your database!

CREATE TABLE foo ( first DATE NOT NULL,second INTEGER NOT NULL,third INTEGER NOT NULL,UNIQUE (first, second, third) );

The UNIQUE constraint implicitly creates an index on first, second and third, 
which will be used to do the sorting in the following query:

SELECT * FROM foo ORDER BY first, second, third;

And for some backwards compatibility, how about a VIEW...

CREATE VIEW bar AS SELECT extract(year FROM first) ||extract(month FROM first) || extract(day FROM first) ||'.' ||
second|| '.' third AS baz FROM foo;
 

- -- 
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org   http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F                  613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAjv8VzcACgkQCT73CrRXhLG2eQCfVsrBJrPxLJABsG7Z1Zva7jZf
jWUAniScvuDkcqQVjyVCaeGhRIPzUPoV
=ZQ7e
-----END PGP SIGNATURE-----