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 D255CD1DD56
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 21 Apr 2004 11:52:18 -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 89201-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 21 Apr 2004 11:52:21 -0300 (ADT)
Received: from happy.onesystem.ca (happy.onesystem.ca [205.206.61.17])
	by svr1.postgresql.org (Postfix) with ESMTP id 908A4D1DD1F
	for <pgsql-sql@postgresql.org>; Wed, 21 Apr 2004 11:52:16 -0300 (ADT)
Received: from elb_lx.onesystem.ca ([10.1.1.50] helo=onesystem.com)
	by happy.onesystem.ca with asmtp (Exim 4.14)
	id 1BGJ5J-000391-1x; Wed, 21 Apr 2004 08:52:21 -0600
Message-ID: <40868AA4.2070501@onesystem.com>
Date: Wed, 21 Apr 2004 08:52:20 -0600
From: Edmund Bacon <ebacon@onesystem.com>
User-Agent: Mozilla Thunderbird 0.5 (X11/20040208)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: ogjunk-pgjedan@yahoo.com
Subject: Re: Order by YYYY MM DD in reverse chrono order trouble
References: <20040421103003.79774.qmail@web12703.mail.yahoo.com>
In-Reply-To: <20040421103003.79774.qmail@web12703.mail.yahoo.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
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/207
X-Sequence-Number: 17582

Is there some reason you can't do this:

SELECT DISTINCT
  date_part('year', uu.add_date),  date_part('month', uu.add_date), 
  date_part('day', uu.add_date)

  FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
  ui.id=uu.user_id
  WHERE uus.x_id=1

  ORDER BY 
	uu.add_date DESC;

This might be faster, as you only have to sort on one field, and I think it should give the desired results

ogjunk-pgjedan@yahoo.com wrote:

>Hello,
>
>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.
>
>I am using the following query, but it's not returning dates back in
>the reverse chronological order:
>
>SELECT DISTINCT
>  date_part('year', uu.add_date),  date_part('month', uu.add_date), 
>  date_part('day', uu.add_date)
>
>FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
>ui.id=uu.user_id
>WHERE uus.x_id=1
>
>ORDER BY
>date_part('year', uu.add_date), date_part('month', uu.add_date), 
>date_part('day',  uu.add_date) DESC;
>
>
>This is what the above query returns:
>
> date_part | date_part | date_part
>-----------+-----------+-----------
>      2004 |         2 |         6
>      2004 |         4 |        20
>(2 rows)
>
>
>I am trying to get back something like this:
>2004 4 20
>2004 4 19
>2004 2 6
>...
>
>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.
>
>Thank you!
>Otis
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>  
>

-- 
Edmund Bacon <ebacon@onesystem.com>