Received: from xyzzy.lan.internal (d150-146-168.home.cgocable.net [24.150.146.168]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAM1d9m77750 for ; Wed, 21 Nov 2001 20:39:09 -0500 (EST) (envelope-from drew@xyzzy.dhs.org) Received: from localhost ([127.0.0.1] helo=there ident=drew) by xyzzy.lan.internal with smtp (Exim 3.32 #1 (Debian)) id 166ipW-0002kL-00; Wed, 21 Nov 2001 20:39:06 -0500 Content-Type: text/plain; charset="iso-8859-1" From: "Andrew G. Hammond" To: Charles Hauser , pgsql-sql@postgresql.org Subject: Re: ORDER BY question Date: Wed, 21 Nov 2001 20:39:02 -0500 X-Mailer: KMail [version 1.3.2] References: In-Reply-To: MIME-Version: 1.0 Message-Id: Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id fAM226n79367 X-Archive-Number: 200111/308 X-Sequence-Number: 5690 -----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-----