SQL Server Query - sorting to push Null at the end

[Rx_]

After moving the data and much of the query logic from UDF (user defined functions) into TSQL, I suspected Nulls would be a challange.

Sort: Area (Text) then Category (Text)

A query - with Sort By on a text field.
The text field can have a value (Format: Category <space> Number)
OR Null (name has yet to be assigned)
No problem to sort the Category.

The customer wants all of the Nulls at the bottom.
The Sort would look like this:
Area 1 Category1
Area 1 Category2
Area 1 Category3
Area 2 Category1
Area 2 Category2
Area 2 Category3
Area 1
Area 1
Area 1
Area 2
Area 2
Area 2

A default Acending puts the Null Categories at the top.
Any ideas appreciated.

 

[pbaldy]

My first thought is adding a field to the query where not null = 1 and null = 2, and put the primary sort on that field. Something like

CASE WHEN Category Is Null THEN 2 ELSE 1 END As SortField

 

[Rx_]

The "old Virtual Column Trick" as quoted by Maxwell Smart.
Read that Oracle has a
order by Some_Column Desc Nulls Last
option - that would make life easier.

Seriouslly Thanks, I didn't want to do all of that work to create the virtual sorting column then find out it could have been easy.

 

[pbaldy]

I'm no dba, so perhaps there is such a thing in SQL Server too.

Hang on, I have to answer my shoe.

 

[Rx_]

Probably won't win 7 Emmies - like these guys did for using those catch phrases:
https://en.wikiquote.org/wiki/Get_Smart

After much research, there is nothing like the IS Null Then
statement in SQL Server.

Would you beleive:
SELECT TOP (100) PERCENT dbo.vWells_Standard.Area, dbo.vWells_Standard.PadNameFinal, ISNULL(dbo.vWells_Standard.PadNameFinal, N'zzz') AS PadNameNullSort,
?

A little recap for anyone who converted MS Access DB back end to SQL Server Back End.
1. You can create nice views with complex sorts in SQL Server Views - Then use Linked Tables to MS Access - the view is there, but the Sort was a waste of time. It will probably revert to index. The main point, don't bother with sorts on the SQL Server View side for Linked tables.

2. If the UDF (user defined function - or an Access function) is used on the query of a Linked Table - the ANSII SQL will cough, spit and move slowly. In technical terms the UDF can't cross the ODBC layer so response time is horrible. Solution: use a pure SQL filter on the Access side of a query using linked tables to SQL Server.

3. On the SQL View - create a Derived (Phantom, fake, temp, ...) table that will allow the SQL langrage query sort the correct way. Since NULL should be last, my choice is to return the orginal PadName (PadNameFinal) or return 'zzzzz'.
zzzz is an improbable legal name for a pad. However, the sort order will make it last.
Note: we don't return the value of this field, it is only used to sort.

Basically, the ISNull function returns the value it tested or returns zzzz

Now that that is done, lets Activate the Cone of Silence

 

[pbaldy]

I'm not sure what you mean by:

After much research, there is nothing like the IS Null Then
statement in SQL Server.

I was using Is Null within a CASE statement. This correctly puts a 1 or 2 in the calculated field, and sorts Nulls to the bottom:

SELECT PartNumber, DelRecdBy
  ,CASE WHEN DelRecdBy Is Null THEN 2 ELSE 1 END As SortField
FROM tblReturns
ORDER BY SortField, DelRecdBy

 

[Rx_]

My bad, was suppose to say:
In Oracle, MySQL and other DB (not SQL Server) they have a
Select * From Some_Table order by Some_Column DESC NULLS LAST

In the SQL Server world, we have to use statements like the CASE, IS Null, IF or other ANSII features.

My bad of not being as clear.
Missed it by that much!