From: Andrew G. Hammond <drew@xyzzy.dhs.org>
To: Charles Hauser <chauser@acpub.duke.edu>
To: pgsql-sql@postgresql.org
Subject: Re: ORDER BY question
Date: Wed, 21 Nov 2001 20:39:02 -0500
Message-ID: <E166ipW-0002kL-00@xyzzy.lan.internal> (raw)
In-Reply-To: <a05010403b819f5e7857d@[152.3.11.193]>
References: <a05010403b819f5e7857d@[152.3.11.193]>
-----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-----
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: drew@xyzzy.dhs.org, chauser@acpub.duke.edu
Subject: Re: ORDER BY question
In-Reply-To: <E166ipW-0002kL-00@xyzzy.lan.internal>
* 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