Re: Order by YYYY MM DD in reverse chrono order trouble

> 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
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,
date_part('day', uu.add_date) DESC;
That way you are sure each of the fields is sorted DESC. if you don't specify a direction in your order by clause postgres will take ASC as the default. 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 once.

Regards,

Stijn Vanroye