Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Date decending and time ascending sorting in a SQL query.

Posted on 2003-03-20
6
Medium Priority
?
1,247 Views
Last Modified: 2010-08-05
Hi,
I am using Oracle 8i. I need to perform sorting on a date field. The requirement is data should be first sorted as DATE ASC, then TIME DESC; here date and time both come from same field. For the purpose I am using following query:
SELECT date_submitted FROM <table_name>
ORDER BY date_submitted DESC, TO_CHAR(date_submitted,'HH:MI AM') ASC;

But this query is not giving me proper resultset. Sorting on time part is not correct.
Could anyone help me on this?
Thanks in advance.

Regards,
Dushyant
0
Comment
Question by:sengards
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:bkowalski
ID: 8174428
How is the sort wrong?  Try using the 24 hour clock instead:

TO_CHAR(date_submitted,'HH24:MI') ASC
0
 
LVL 15

Accepted Solution

by:
andrewst earned 225 total points
ID: 8174507
Your have 2 problems:

1) the first sort criterion includes the time (descending), so the second is redundant.

2) Your chosen time format would return this order:
01:00 AM
01:00 PM
02:00 AM
02:00 PM
...

Do this instead:

SELECT date_submitted FROM <table_name>
ORDER BY TRUNC(date_submitted) DESC, TO_CHAR(date_submitted,'HHMISS') ASC;

or:

SELECT date_submitted FROM <table_name>
ORDER BY TRUNC(date_submitted) DESC, date_submitted - TRUNC(date_submitted) ASC;

0
 

Author Comment

by:sengards
ID: 8174539
Bkowalski,
Thankx for your response. My resultset is something like this:
DATE_SUBMITTED
3/20/03 6:26:31 PM
3/20/03 3:49:26 PM
3/20/03 10:52:52 AM
3/19/03 5:00:36 PM
3/19/03 4:56:41 PM

Per my requirement, it should be as given below:
3/20/03 10:52:52 AM
3/20/03 3:49:26 PM
3/20/03 6:26:31 PM
3/19/03 4:56:41 PM
3/19/03 5:00:36 PM

I tried both TO_CHAR(date_submitted,'HH24:MI') ASC and
TO_CHAR(date_submitted,'HH:MI') ASC. It gives same result.

Regards,
Dushyant
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:bkowalski
ID: 8174556
I think andrewst has the solution for you.
0
 

Author Comment

by:sengards
ID: 8174665
Bkowalski,
Thankx for your response. My resultset is something like this:
DATE_SUBMITTED
3/20/03 6:26:31 PM
3/20/03 3:49:26 PM
3/20/03 10:52:52 AM
3/19/03 5:00:36 PM
3/19/03 4:56:41 PM

Per my requirement, it should be as given below:
3/20/03 10:52:52 AM
3/20/03 3:49:26 PM
3/20/03 6:26:31 PM
3/19/03 4:56:41 PM
3/19/03 5:00:36 PM

I tried both TO_CHAR(date_submitted,'HH24:MI') ASC and
TO_CHAR(date_submitted,'HH:MI') ASC. It gives same result.

Regards,
Dushyant
0
 

Author Comment

by:sengards
ID: 8174678
Thanx Andrewst. It's working as desired with your suggestions. Thanx a lot. :-)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month13 days, 1 hour left to enroll

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question