Solved

SQL ORDER BY

Posted on 2012-04-11
8
304 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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now