Hello,

I have 2 sort orders in my table, ASC and DESC:

NULL               9
NULL               8
6                     7
7                     6
8                     NULL
9                     NULL

Is it possible to get the NULL value always on top in the table? In the index settings I see no option for it.

Thanks

Stagiaire12


I think the answer is no but if you're using SQL rather than tables you can cheat and use COALESE

ORDER BY COALESCE(sortcol,9999999) DESC

will put NULLs at the top as will

ORDER BY COALESCE(sortcol, -99999) ASC

Roy Lambert [Team Elevate]

Stagiaire12 wrote:

Hello,

I have 2 sort orders in my table, ASC and DESC:

NULL               9
NULL               8
6                     7
7                     6
8                     NULL
9                     NULL

>>Is it possible to get the NULL value always on top in the table? In the index settings I see no option for it.

Alternatively, order by

ORDER BY CASE WHEN ISNULL(FieldName) THEN 0 ELSE 1 END, ColumnName

<< Is it possible to get the NULL value always on top in the table? In the
index settings I see no option for it. >>

You can do so, but you have to do it in a "roundabout" way.  What you do is
create a generated or computed column that includes an expression similar to
what Roy and Chris have proposed, and then create an index on this column
instead.  That will give you an index with a custom sort for the NULL
values.

--
Tim Young
Elevate Software
www.elevatesoft.com