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

Subscribe Post Reply

2168

Holger Klawitter

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

Holger Klawitter

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

Nov 23 '05 #3

Clodoaldo Pinto Neto

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

Clodoaldo Pinto Neto

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

Nov 23 '05 #5

Scott Ribe

> 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

Scott Ribe

> ORDER BY

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

Nov 23 '05 #7

John Sidney-Woollett

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

John Sidney-Woollett

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.

Nov 23 '05 #9

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

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

Nov 23 '05 #11

Similar topics

reverse the order of the words

by: Matt | last post by:

Given an array of characters which form a sentence of words, give an efficient algorithm to reverse the order of the words (not characters) in it.

C / C++

best way to reverse bit order of data type ?

by: Laszlo Szijarto | last post by:

In C#, wha't the best way to reveser the bit order of a data type and then convert it back to that datatype? So, take a byte, reverse bits, convert it back to a byte. I tried to get a BitArray...

C# / C Sharp

Ordering YYYY MM DD in reverse chrono order

by: OtisUsenet | last post by:

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

PostgreSQL Database

reverse bit order

by: mike7411 | last post by:

Is there any easy way to reverse the order of the bits in a byte in C++? (i.e. 00000001 becomes 10000000)

C / C++

Reverse XmlNodeList Order

by: eggie5 | last post by:

Hi, I have an XmlNodeList and I need to reverse it. Just like Array.Reverse(), but it has to stay as an XmlNodeList. Any ideas?

C# / C Sharp

reverse the order of selected items

by: Doug | last post by:

Hi I have a datagridview in a windows C# application and i am allowing a user to select items from the datagridview. I allow the user to copy the selected items to the clipboard and then if...

C# / C Sharp

Ordering Months in Calendar Order

by: bassrider | last post by:

Hi There, I am trying to order my months in calendar format (Jan, Feb, Mar...), but I can not. It only shows in alphabetical order (Apr,Aug,Dec..). Does anyone now how I can change the order from...

Microsoft SQL Server

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing