Thread: Ordering YYYY MM DD in reverse chrono order

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!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> ORDER BY
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day',  uu.add_date) DESC;

You are sorting by three columns, only the last one is desc.

What you need is:

...
order by
    date_part( 'year', uu.add_date ) desc,
    date_part( 'month', uu.add_date ) desc,
    date_part( 'day', uu.add_date ) desc
;

Mit freundlichem Gruß / With kind regards
    Holger Klawitter
- --
lists <at> klawitter <dot> de
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFAjTtF1Xdt0HKSwgYRAmaDAKCcSo5kEPkn4QJfsFhg9EE0k/dmmwCfa7gB
cUjzCy/X0mJXW0Aooyb7pbE=
=0Fhk
-----END PGP SIGNATURE-----

Did you try

ORDER BY
date_part('year', uu.add_date) desc, date_part('month', uu.add_date) desc,
date_part('day',  uu.add_date) DESC;

Regards,
Clodoaldo
 --- OtisUsenet <otis_usenet@yahoo.com> escreveu: > 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!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

______________________________________________________________________

Yahoo! Messenger - Fale com seus amigos online. Instale agora!
http://br.download.yahoo.com/messenger/

> ORDER BY
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day',  uu.add_date) DESC;

You meant:

ORDER BY
date_part('year', uu.add_date) DESC, date_part('month', uu.add_date) DESC,
date_part('day',  uu.add_date) DESC;


--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice

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.

On Apr 21, 2004, at 5:19 AM, OtisUsenet wrote:
<snip>

> I am using the following query, but it's not returning dates back in
> the reverse chronological order:

<snip>

> ORDER BY
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day',  uu.add_date) DESC;

Maybe I'm missing something, but why not just sort by "uu.add_date
DESC", like so:

    ORDER  BY uu.add_date DESC;

Seems like it would be more efficient, and there'd be no issues about
forgetting to put DESC after each date_part() (like you have above).

eric