Re: Ordering YYYY MM DD in reverse chrono order - Mailing list pgsql-general

From John Sidney-Woollett
Subject Re: Ordering YYYY MM DD in reverse chrono order
Date
Msg-id 1925.192.168.0.64.1082994127.squirrel@mercury.wardbrook.com
Whole thread Raw
In response to Ordering YYYY MM DD in reverse chrono order  (otis_usenet@yahoo.com (OtisUsenet))
List pgsql-general
Try

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

The reason that your previous sort failed is that you need the DESC
keyword applied to each sort term.

John Sidney-Woollett

OtisUsenet said:
> 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.


pgsql-general by date:

Previous
From: Scott Ribe
Date:
Subject: Re: Ordering YYYY MM DD in reverse chrono order
Next
From: Bruce Momjian
Date:
Subject: Re: shadowing (like IB/Firebird)