Solved

order by date asc putting blank dates on the bottom

Posted on 2006-10-24
3
481 Views
Last Modified: 2008-02-01
Hi,

I'm doing a query where I want to sort the data by the column "shipdate".  The most recent ship dates should be on top.  Simple enough using order by date ASC.

Similar to this...

SELECT
name, dealer, orderid, shipdate
FROM tblOrders
ORDER BY shipdate ASC

HOWEVER, some of the shipdates are null and/or empty.  These ship dates are returning on the top of the returned query.  I want the rows with no ship date to appear on the BOTTOM of the query, after the rows that have a ship date in the proper order.

How can I do this?


0
Comment
Question by:benjamintr
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 50 total points
Comment Utility
ORDER BY shipdate IS NULL OR shipdate = '', shipdate
0
 

Author Comment

by:benjamintr
Comment Utility
That works!  Could you provide a quick explanation for me of what's going on?
0
 
LVL 33

Expert Comment

by:snoyes_jw
Comment Utility
IS NULL return 0 if the value is not null, 1 if it is. 0 sorts before 1, so we have all not null and not empty values before all null or empty values.  Then within those two groups, we sort by shipdate.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

9 Experts available now in Live!

Get 1:1 Help Now