SQL ORDER BY

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?
LVL 1
mopar003Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
This should do what you need:

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

Open in new window

yawkey13Commented:
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
mopar003Author Commented:
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brendt HessSenior DBACommented:
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
Anthony PerkinsCommented:
Something like this perhaps:
SELECT * 
FROM Request 
ORDER BY COALESCE(DueDate, DateSubmitted) DESC, ID

Open in new window

HainKurtSr. System AnalystCommented:
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...
mopar003Author Commented:
My mistake on the first round of attempts I tried with yours.  My apologies.  You nailed it.  Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.