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! 10 2166
-----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
-----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
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
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
> 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
> 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
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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.
|
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...
|
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...
|
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)
|
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?
|
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...
|
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...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
| |