Solved

SQL ORDER BY

Posted on 2012-04-11
8
307 Views
Last Modified: 2012-04-11
I have a work order system that I need to order based on DueDate with the oldest Date on Top, and if no DueDate is present, then order by Date Submitted with the oldest at top, just below any DueDate listed items.  Becasue DueDate may be blank, My current solution is not putting out what I want.

This is what I have now:
SELECT * FROM Request ORDER BY DueDate DESC, ID

OUTPUT
Request 1 - Due Date: 4/15
Request 2 - Due Date: 4/15
Request 3 - Due Date: 4/12
Request 4 - SubmittedDate: 3/4
Request 5 - SubmittedDate: 3/7
Request 6 - SubmittedDate: 4/2


This is almost correct, but I need the older date of the DueDate group on top!  Any ideas how I can accomplish this?
0
Comment
Question by:mopar003
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 37835355
This should do what you need:

SELECT * 
FROM Request 
ORDER BY DueDate DESC, 
    SubmittedDate DESC,
    ID

Open in new window

0
 
LVL 6

Expert Comment

by:yawkey13
ID: 37835378
SELECT
      'Request ' + CAST([ID] AS VARCHAR) + ' - ' +
      CASE
            WHEN duedate IS NOT NULL THEN
                  'Due Date: '
            ELSE
                  'Submitted Date: '
      END +
      CAST(ISNULL(duedate,submitteddate) AS VARCHAR)
FROM Request
ORDER BY
      CASE
            WHEN duedate IS NOT NULL THEN
                  'Due Date: '
            ELSE
                  'Submitted Date: '
      END,
      duedate ASC,
      submitteddate ASC
0
 
LVL 1

Author Comment

by:mopar003
ID: 37835416
bhess1 - This reverses the order, but now all the items that do not have a duedate are at the top and the items that DO have a due Date are in the right order, but at the bottom.

yawkey13 - This is blowing my mind!  I tried it out, but it also seems to not be getting the right results.

In short, I need the items that have due dates to be sorted in order from oldest date at top, then any items that DO NOT have a due date, get sorted from oldest to newest.  But because its taking the DueDate as a blank, it gets sorted above the items that do have a DueDate. Hope I didn't confuse that even more.

In the end I want this (which I can acheive)
Request 1 - Due Date: 4/15
Request 2 - Due Date: 4/15
Request 3 - Due Date: 4/12
Request 4 - SubmittedDate: 3/4
Request 5 - SubmittedDate: 3/7
Request 6 - SubmittedDate: 4/2

To be THIS:
Request 3 - Due Date: 4/12
Request 1 - Due Date: 4/15
Request 2 - Due Date: 4/15
Request 4 - SubmittedDate: 3/4
Request 5 - SubmittedDate: 3/7
Request 6 - SubmittedDate: 4/2
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Accepted Solution

by:
yawkey13 earned 500 total points
ID: 37835446
query results
I don't see how my results differ from your desired results.  For consistency, add ", [id] asc" to the end of the query.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 37835518
Is Due Date a datetime or a varchar/char field.

If it is a textual field, try this:

SELECT *
FROM Request
ORDER BY CASE
    WHEN Nullif(DueDate, '') IS NULL
        THEN '20991231'
        ELSE DueDate
    END,
    ID
 
If it is not textual, and the DueDate field is null, then a similar query applies:

SELECT *
FROM Request
ORDER BY CASE
    WHEN DueDate IS NULL
        THEN '20991231'
        ELSE DueDate
    END,
    ID
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37835602
Something like this perhaps:
SELECT * 
FROM Request 
ORDER BY COALESCE(DueDate, DateSubmitted) DESC, ID

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 37835698
this is really confusing...

can you please post the query result of this

SELECT * FROM Request ORDER BY DueDate DESC, ID

just copy paste the result... the way you post confuses everybody...
0
 
LVL 1

Author Closing Comment

by:mopar003
ID: 37835819
My mistake on the first round of attempts I tried with yours.  My apologies.  You nailed it.  Thank you!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

808 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