Re: ORDER BY question

-----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-----