Ordering YYYY MM DD in reverse chrono order

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

How about just

ORDER BY uu.add_date DESC

?

It would be cheaper without all of those date_part calls, and should
order in the same way.

_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail

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:

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

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/

-----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-----

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