By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,835 Members | 1,091 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,835 IT Pros & Developers. It's quick & easy.

Ordering YYYY MM DD in reverse chrono order

P: n/a
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!
Nov 23 '05 #1
Share this Question
Share on Google+
10 Replies

P: n/a
-----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)

iD8DBQFAjTtF1Xdt0HKSwgYRAmaDAKCcSo5kEPkn4QJfsFhg9E E0k/dmmwCfa7gB
cUjzCy/X0mJXW0Aooyb7pbE=
=0Fhk
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2

P: n/a
-----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)

iD8DBQFAjTtF1Xdt0HKSwgYRAmaDAKCcSo5kEPkn4QJfsFhg9E E0k/dmmwCfa7gB
cUjzCy/X0mJXW0Aooyb7pbE=
=0Fhk
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3

P: n/a
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 <ot*********@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/

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

Nov 23 '05 #4

P: n/a
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 <ot*********@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/

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

Nov 23 '05 #5

P: n/a
> 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
sc********@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 23 '05 #6

P: n/a
> 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
sc********@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 23 '05 #7

P: n/a
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.

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

Nov 23 '05 #8

P: n/a
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.

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

Nov 23 '05 #9

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 23 '05 #10

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 23 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.