?
Solved

Date decending and time ascending sorting in a SQL query.

Posted on 2003-03-20
6
Medium Priority
?
1,243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

762 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