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]

Re: Ordering YYYY MM DD in reverse chrono order

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
[color=blue]
> ORDER BY
> date_part('year ', uu.add_date), date_part('mont h', uu.add_date),
> date_part('day' , uu.add_date) DESC;[/color]

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]

Re: Ordering YYYY MM DD in reverse chrono order

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
[color=blue]
> ORDER BY
> date_part('year ', uu.add_date), date_part('mont h', uu.add_date),
> date_part('day' , uu.add_date) DESC;[/color]

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]

Re: Ordering YYYY MM DD in reverse chrono order

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 <otis_usenet@ya hoo.com> escreveu: > Hello,[color=blue]
>
> 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[/color]

_______________ _______________ _______________ _______________ __________

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]

Re: Ordering YYYY MM DD in reverse chrono order

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 <otis_usenet@ya hoo.com> escreveu: > Hello,[color=blue]
>
> 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[/color]

_______________ _______________ _______________ _______________ __________

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]

Re: Ordering YYYY MM DD in reverse chrono order

> ORDER BY[color=blue]
> date_part('year ', uu.add_date), date_part('mont h', uu.add_date),
> date_part('day' , uu.add_date) DESC;[/color]

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
scott_ribe@kill erbytes.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

[Scott Ribe]

Re: Ordering YYYY MM DD in reverse chrono order

> ORDER BY[color=blue]
> date_part('year ', uu.add_date), date_part('mont h', uu.add_date),
> date_part('day' , uu.add_date) DESC;[/color]

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
scott_ribe@kill erbytes.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

[John Sidney-Woollett]

Re: Ordering YYYY MM DD in reverse chrono order

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:[color=blue]
> 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.[/color]


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

[John Sidney-Woollett]

Re: Ordering YYYY MM DD in reverse chrono order

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:[color=blue]
> 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.[/color]


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

[Eric Ridge]

Re: Ordering YYYY MM DD in reverse chrono order

On Apr 21, 2004, at 5:19 AM, OtisUsenet wrote:
<snip>
[color=blue]
> I am using the following query, but it's not returning dates back in
> the reverse chronological order:[/color]

<snip>
[color=blue]
> ORDER BY
> date_part('year ', uu.add_date), date_part('mont h', uu.add_date),
> date_part('day' , uu.add_date) DESC;[/color]

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

[Eric Ridge]

Re: Ordering YYYY MM DD in reverse chrono order

On Apr 21, 2004, at 5:19 AM, OtisUsenet wrote:
<snip>
[color=blue]
> I am using the following query, but it's not returning dates back in
> the reverse chronological order:[/color]

<snip>
[color=blue]
> ORDER BY
> date_part('year ', uu.add_date), date_part('mont h', uu.add_date),
> date_part('day' , uu.add_date) DESC;[/color]

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