NULL Date Sorting: Oracle vs MSSQL

Thanks, but I do not want to filter out NULL events -- I just want them to come last.

For example, we have a Flash-based announcements scroller where we have to have all events with dates to appear first, in ascending order, then show all events with NULL dates last (the order for NULLs isn't important). On Oracle, all is well; on MSSQL, the NULLs are appearing first.

-Dain

You could work around this by using

SELECT * FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS
ORDER BY COALESCE (EVENT_DATE, (SELECT MIN(EVENT_DATE) FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS ) - 1) ASC

or, alternatively

SELECT * FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS
ORDER BY COALESCE (EVENT_DATE, (SELECT MAX(EVENT_DATE) FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS ) + 1) ASC

In Oracle you would do it by using the "NULLS LAST" clause:
... ORDER BY event_date ASC NULLS LAST;

However, I have no idea if this is ANSI SQL and if it would work on SQL Server. You can try it, though. :)
Hope that helps!

Nulls sort high in Oracle.  Looks like the sort low in MSSQL.  If the query is being issued in SQLPLUS, try this at the SQL prompt:
>col new_null new_value mynull
>select chr(9) new_null from dual;
>set null '&mynull'

then issue the query.  chr(9) is a tab, which will be non-visible, but should sort lower than alpha-numerics.  if this is acceptable, put it in the login.sql or glogin.sql script.

One more thing,  if you choose to include the statements provided in your login.sql, set termout off before you execute them, and set termout on afterward.

Thanks to everyone. I gave points to Nievergelt and schwertner; while schwertner's solution worked, I found that for my specific situation, Nievergelt's answer fit perfectly. Thanks to you both!

BTW, Nievergelt, does that work in MySQL? We don't need it now, but may in the future.

Thanks,

-Dain

SELECT * FROM OUR_SCHEMA.TBL_ANNOUNCEMENTS
ORDER BY decode(EVENT_DATE,null,'31-dec-9999',event_date);

test case:

drop table tab1;
create table tab1 (event varchar2(10), event_date date);
insert into tab1 values ('Annc 1','10-oct-2005');
insert into tab1 values ('Annc 2','10-oct-2006');
insert into tab1 values ('Annc 3','10-oct-2007');
insert into tab1 values ('Annc 4',NULL);
insert into tab1 values ('Annc 5',NULL);

select * from tab1
ORDER BY decode(EVENT_DATE,null,'31-dec-9999',event_date);

Results:

EVENT      EVENT_DAT
---------- ---------
Annc 1     10-OCT-05
Annc 2     10-OCT-06
Annc 3     10-OCT-07
Annc 4
Annc 5

Hi Dain,

Sorry, I have no MySql experience at all.

COALESCE function has existed since MySQL 3.23.3.  Subqueries came into being starting with MySQL 4.1.0.   So if you're using a MySQL version >= 4.1.0, then you should be fine.