Order by YYYY MM DD in reverse chrono order trouble

From: <ogjunk-pgjedan(at)yahoo(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Order by YYYY MM DD in reverse chrono order trouble
Date: 2004-04-21 10:30:03
Message-ID: 20040421103003.79774.qmail@web12703.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stijn Vanroye 2004-04-21 10:58:42 Re: Order by YYYY MM DD in reverse chrono order trouble
Previous Message Andrew Sullivan 2004-04-21 10:13:27 Re: transaction