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 2AB6FD1BA2E for ; Thu, 22 Apr 2004 07:47:43 -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 95664-03 for ; Thu, 22 Apr 2004 07:47:43 -0300 (ADT) Received: from mail.cobweb.nl (mx1.cobweb.nl [80.242.96.4]) by svr1.postgresql.org (Postfix) with ESMTP id 5B04FD1DC96 for ; Thu, 22 Apr 2004 07:47:40 -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 i3MAloU3092156 for ; Thu, 22 Apr 2004 12:47:50 +0200 (CEST) (envelope-from s.vanroye@farcourier.com) Subject: Re: Order by YYYY MM DD in reverse chrono order trouble MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Date: Thu, 22 Apr 2004 12:47:10 +0200 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 content-class: urn:content-classes:message Message-ID: <71E201BE5E881C46811BA160694C5FCB04672B@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: AcQoUzYjI3ALGXfIS4qCLGKrZ6gElAAAONxA From: "Stijn Vanroye" To: Cc: 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/226 X-Sequence-Number: 17601 Yes indeed, I seem to have misinterpreted that last one. My apologies. The distinct solution I mentioned isn't going to solve it, you are absolute= ly right in your example. To get back on track: You don't have to use a field in the select part of you query to be able to= use it in the order by clause. So using order by add_date should indeed wo= rk. Since it appears that your add_date is a timestamp field (including tim= e) it will order first on the date part, and next on the time part. E.g.: 2004-04-12 12:45:22 2004-04-12 09:55:25 2004-04-11 14:25:31 2004-04-11 11:11:25 Since you have a distinct only on the date parts of the timestamp this will= make no difference in the end result, it will still be sorted correctly on= the date. but I'm wondering if a GROUP BY wouldn't also be a good solution? like this: <---CODE----> SELECT=20 date_part('year', uu.add_date), date_part('month', uu.add_date),=20 date_part('day', uu.add_date) 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 GROUP BY date_part('year', uu.add_date), date_part('month', uu.add_date),=20 date_part('day', uu.add_date); <---END CODE----> That way you don't need the DISTINCT part. The disadvantage however is that= all the fields in the select must eighter be a part of the GROUP BY clause= , or be used in an aggregate function. Anyways: just descide whatever solution fits best for your needs. Regards, Stijn. >Otis wrote: > Hello, >=20 > But will this work even with my add_date column, which is a TIMESTAMP > field? Values in this column contain hours, minutes,=20 > seconds, etc., so > won't DISTINCT return multiple rows for add_dates that _are_ distinct, > but are on the same day. >=20 > For example: >=20 > 2004 04 02 11:22:33.034 > 2004 04 02 22:33:44.055 >=20 > Thanks, > Otis >=20 >=20 > --- Stijn Vanroye wrote: > > > Hello, > > >=20 > > > I'd love to be able to do that, but I cannot just ORDER BY=20 > > > uu.add_date, > > > because I do not have uu.add_date in the SELECT part of the=20 > > > statement.=20 > > > The reason I don't have it there is because I need=20 > distinct YYYY MM > > DD > > > values back. > > > Is there a trick that I could use to make this more elegant? > > yes, you could use: > > SELECT DISTINCT ON (field1, field2) field1, field3, FieldN=20 > from table > >=20 > > Regards, > >=20 > >=20 > > Stijn Vanroye >=20 >=20