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(at)xyzzy(dot)dhs(dot)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-----