Ordering YYYY MM DD in reverse chrono order

[OtisUsenet]

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('mont h', 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_i d
WHERE uus.x_id=1

ORDER BY
date_part('year ', uu.add_date), date_part('mont h', 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!

[Holger Klawitter]

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

ORDER BY
date_part('year ', uu.add_date), date_part('mont h', 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)

iD8DBQFAjTtF1Xd t0HKSwgYRAmaDAK CcSo5kEPkn4QJfs Fhg9EE0k/dmmwCfa7gB
cUjzCy/X0mJXW0Aooyb7pb E=
=0Fhk
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Holger Klawitter]

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

ORDER BY
date_part('year ', uu.add_date), date_part('mont h', 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)

iD8DBQFAjTtF1Xd t0HKSwgYRAmaDAK CcSo5kEPkn4QJfs Fhg9EE0k/dmmwCfa7gB
cUjzCy/X0mJXW0Aooyb7pb E=
=0Fhk
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Clodoaldo Pinto Neto]

Did you try

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

Regards,
Clodoaldo
--- OtisUsenet <ot*********@ya hoo.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('mont h', 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_i d
WHERE uus.x_id=1

ORDER BY
date_part('year ', uu.add_date), date_part('mont h', 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/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Clodoaldo Pinto Neto]

Did you try

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

Regards,
Clodoaldo
--- OtisUsenet <ot*********@ya hoo.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('mont h', 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_i d
WHERE uus.x_id=1

ORDER BY
date_part('year ', uu.add_date), date_part('mont h', 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/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Scott Ribe]

> ORDER BY

date_part('year ', uu.add_date), date_part('mont h', uu.add_date),
date_part('day' , uu.add_date) DESC;

You meant:

ORDER BY
date_part('year ', uu.add_date) DESC, date_part('mont h', uu.add_date) DESC,
date_part('day' , uu.add_date) DESC;
--
Scott Ribe
sc********@kill erbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Scott Ribe]

> ORDER BY

date_part('year ', uu.add_date), date_part('mont h', uu.add_date),
date_part('day' , uu.add_date) DESC;

You meant:

ORDER BY
date_part('year ', uu.add_date) DESC, date_part('mont h', uu.add_date) DESC,
date_part('day' , uu.add_date) DESC;
--
Scott Ribe
sc********@kill erbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[John Sidney-Woollett]

Try

SELECT DISTINCT
date_part('year ', uu.add_date), date_part('mont h', 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_i d
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('mont h', 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_i d
WHERE uus.x_id=1

ORDER BY
date_part('year ', uu.add_date), date_part('mont h', 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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[John Sidney-Woollett]

Try

SELECT DISTINCT
date_part('year ', uu.add_date), date_part('mont h', 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_i d
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('mont h', 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_i d
WHERE uus.x_id=1

ORDER BY
date_part('year ', uu.add_date), date_part('mont h', 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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Eric Ridge]

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('mont h', uu.add_date),
date_part('day' , uu.add_date) DESC;

Maybe I'm missing something, but why not just sort by "uu.add_dat e
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html