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
Ora-00054: Resource Busy and Acquire with Nowait Specified
Postgres - Aggregate Two Columns into One Item
How to Swap Column Values in SQL Server 2008
Difference Between Decimal and Numeric
Multiple Constraints in Table: How to Get All Violations
Checking for Time Range Overlap, the Watchman Problem [Sql]
Linq to SQL Every Nth Row from Table
Declaring & Setting Variables in a Select Statement
Equivalent Function to Stuff in SQL (Group_Concat in Myssql/Listagg in Oracle)
How to Analyse a SQLite Query Execution
Why Doesn't SQL Server Support Unsigned Datatype
How to Export Data with Oracle SQL Developer
How to Find the Worst Performing Queries in SQL Server 2008
How to Get the Raw Query String from Laravel's Query Builder Before Executing the Query
How to Take Sum of Column with Same Id in SQL