Danielopez .. Danielopez .. - 4 months ago 22
SQL Question

Error sorting nulls first MSSQL

My goal is to enlist all the elements from the table

cat.CAT_RUTAS
and I'm adding a type
null
to the list with the value 'Enlistar todas las rutas', So I want that the
null
value to be the first option of the result and sort the rest values in ascending.

I'm using MSSQL SERVER but when i'm trying to run this query:

select CATrut_iIdentificador, CATrut_vDescripcion
from cat.CAT_Rutas
UNION
SELECT NULL , 'Enlistar todas las rutas'
order by CATrut_vDescripcion ASC NULLS FIRST


The problem is when i'm trying to add the null value to the top. Receiving the error:

Incorrect syntax near 'NULLS'.

Answer

Here is another option which is simply adding a new column for sorting.

select CATrut_iIdentificador, CATrut_vDescripcion, 1 as SortOrder
from cat.CAT_Rutas 
UNION 
SELECT NULL , 'Enlistar todas las rutas', 0 as SortOrder
order by SortOrder, CATrut_vDescripcion