Solved

order by date asc putting blank dates on the bottom

Posted on 2006-10-24
3
484 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
ID: 17798050
ORDER BY shipdate IS NULL OR shipdate = '', shipdate
0
 

Author Comment

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

Expert Comment

by:snoyes_jw
ID: 17798303
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

827 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