?
Solved

SQL ORDER BY

Posted on 2012-04-11
8
Medium Priority
?
311 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
[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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
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
Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

 
LVL 6

Accepted Solution

by:
yawkey13 earned 2000 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:Brendt Hess
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 57

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

770 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