SQL Server Equivalent to Oracle's Nulls First

SQL Server equivalent to Oracle's NULLS FIRST?

You can do some trick:

ORDER BY (CASE WHEN [Order] IS NULL THEN 0 ELSE 1 END), [Order] 

What is the Oracle equivalent of SQL Server's IsNull() function?

coalesce is supported in both Oracle and SQL Server and serves essentially the same function as nvl and isnull. (There are some important differences, coalesce can take an arbitrary number of arguments, and returns the first non-null one. The return type for isnull matches the type of the first argument, that is not true for coalesce, at least on SQL Server.)

Columns with Null Values in SQL Server vs Oracle

In SQL Server '' is not null. In Oracle its a bit complicated x char := '' is not null in PL/SQL but as varchar2 empty string '' is exactly same as null as explained here And in case of queries this is varchar2 so condition COL = '' is never true.

If you need condition that will work in both DBs please use coalesce function.

select * from TAB 
where coalesce(COL,'SOME_UNIQUE_STRING') = coalesce(FILTER_PARAM, 'SOME_UNIQUE_STRING')

Such condition should return rows when COLUMN is equal to FILTER as well as both are null or empty strings.

ORDER BY DATE showing NULLS first then most recent dates

@Chris, you almost have it.

ORDER BY (CASE WHEN [Submission Date] IS NULL THEN 1 ELSE 0 END) DESC, 
[Submission Date] DESC

[Edit: #Eppz asked me to tweak the code above as currently shown]

I personally prefer this a lot better than creating "magic numbers". Magic numbers are almost always a problem waiting to happen.

Replacing NULL with 0 in a SQL server query

When you want to replace a possibly null column with something else, use IsNull.

SELECT ISNULL(myColumn, 0 ) FROM myTable

This will put a 0 in myColumn if it is null in the first place.

Not equal != operator on NULL

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations.

This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.

Note: To compare if your value is not null, you use IS NOT NULL, while to compare with not null value, you use <> 'YOUR_VALUE'. I can't say if my value equals or not equals to NULL, but I can say if my value is NULL or NOT NULL. I can compare if my value is something other than NULL.

Oracle SQL - get the row with either NULL or the max value

You want the ID for the highest date, where null is considered higher than any real date. In case of a tie you want the minimum ID. You can use Oracle's KEEP LAST for this:

select min(id) keep (dense_rank last order by date nulls last) from mytable;

SQL how to make null values come last when sorting ascending

select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate


Related Topics



Leave a reply



Submit