X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DBFFFD1C967 for ; Wed, 21 Apr 2004 07:59:11 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 05252-07 for ; Wed, 21 Apr 2004 07:59:12 -0300 (ADT) Received: from mail.cobweb.nl (mx1.cobweb.nl [80.242.96.4]) by svr1.postgresql.org (Postfix) with ESMTP id C563ED1C50D for ; Wed, 21 Apr 2004 07:59:08 -0300 (ADT) Received: from farcourier.com (dp3-26.dsl.cobweb.nl [193.67.120.154] (may be forged)) by mail.cobweb.nl (8.12.8/8.12.7) with ESMTP id i3LAxHww066543 for ; Wed, 21 Apr 2004 12:59:18 +0200 (CEST) (envelope-from s.vanroye@farcourier.com) MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Subject: Re: Order by YYYY MM DD in reverse chrono order trouble Content-Transfer-Encoding: quoted-printable Date: Wed, 21 Apr 2004 12:58:42 +0200 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 content-class: urn:content-classes:message Message-ID: <71E201BE5E881C46811BA160694C5FCB0FA92C@fs1000.farcourier.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] Order by YYYY MM DD in reverse chrono order trouble Thread-Index: AcQnjr/I8PoDT8wqQw6si4iAVEnhgAAACL/A From: "Stijn Vanroye" To: , X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/201 X-Sequence-Number: 17576 > Hello, >=20 > I am trying to select distinct dates and order them in the reverse > chronological order. Although the column type is TIMESTAMP, in this > case I want only YYYY, MM, and DD back. >=20 > I am using the following query, but it's not returning dates back in > the reverse chronological order: >=20 > SELECT DISTINCT > date_part('year', uu.add_date), date_part('month', uu.add_date),=20 > date_part('day', uu.add_date) >=20 > FROM uus INNER JOIN ui ON uus.user_id=3Dui.id INNER JOIN uu ON > ui.id=3Duu.user_id > WHERE uus.x_id=3D1 >=20 > ORDER BY > date_part('year', uu.add_date), date_part('month', uu.add_date),=20 > date_part('day', uu.add_date) DESC; >=20 >=20 > This is what the above query returns: >=20 > date_part | date_part | date_part > -----------+-----------+----------- > 2004 | 2 | 6 > 2004 | 4 | 20 > (2 rows) >=20 >=20 > I am trying to get back something like this: > 2004 4 20 > 2004 4 19 > 2004 2 6 > ... >=20 > My query is obviously wrong, but I can't see the mistake. I was > wondering if anyone else can see it. Just changing DESC to ASC, did > not work. >=20 > Thank you! > Otis What you could try to do in your order by clause is the following: ORDER BY date_part('year', uu.add_date) DESC, date_part('month', uu.add_date) DESC,=20 date_part('day', uu.add_date) DESC; That way you are sure each of the fields is sorted DESC. if you don't speci= fy a direction in your order by clause postgres will take ASC as the defaul= t. I think that he does "ASC,ASC,DESC" instead. I'm not sure if he applies = the DESC to all specified fields in the order by if you declare it only onc= e. Regards, Stijn Vanroye