Solved

SQL ORDER BY

Posted on 2012-04-11
8
303 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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

17 Experts available now in Live!

Get 1:1 Help Now