Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-07 : 09:28:11
|
| James writes "Hi Guys,I would like to know how to do an ORDER BY on Non-NULL values first, displaying the null value last.I have two data sets, 1. a view of the current data and 2. an archive table. The archive table has a field "ArchiveDate". When I select the current data (from the view) for the UNION, I set this value to NULL.So I now have a result data set with ArchiveDate set to NULL for the current record. My problem is that MS SQL Server sorts NULL values first. However for presentation I would like to display the current record last (i.e. the record with the NULL ArchiveDate).Any ideas?My only idea is to use a "Magic Date" and then strip this out at the presentation layer - YUK!Regards,James." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-07 : 09:46:08
|
| Add this to your ORDER BY clause, before the other columns:ORDER BY CASE ArchiveDate WHEN Null THEN 1 ELSE 0 END, ...This could work too:ORDER BY IsNull(ArchiveDate, '99991231'), ... |
 |
|
|
|
|
|
SQL Order By (Non-Null)