Search code examples
sqlodata

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?

Solution

  • 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