CODEWITHSUNDEEP
sqlodata
maximelian1986
maximelian1986

Reputation: 2462

Odata override behaviour of orderby

My date column in sql table can be null, so I getting that data using OData but if I order it by asc, null values is in front of all other rows. I want that it be desplayed after all others:

 1. 31.12.2000
 2. 31.12.2010
 3. null

Is there is possibility to somehow override filtering so it take null value as may be DateTime.MaxValue?

Upvotes: 1

Views: 1398

Answers (2)

Jonathan Degoede
Jonathan Degoede

Reputation: 1

We had the same problem and a simple solution that worked for us was :

$compute=<property> eq null as IsNull<property>&$orderBy=IsNull<property> ASC, <property> asc

It will compute a new property representing if the value is null or not, sort by that property first (nulls will go last), and then apply the actual sort on the main property.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270483

The ANSI standard supports NULLS FIRST and NULLS LAST, but SQL Server does not have these options.

Instead, you can use two keys in the ORDER BY:

order by (case when col is not null then 1 else 2 end),
         col asc

Upvotes: 1

Related Questions