ORDER BY sort with Null/Empty field last

What flavour of SQL backend? MSSQL, MySQL, Oracle or another, such as an MS Access or other file?

Tried the union. It completely deleted all my options.

Database:  MS Access
Microsoft SQL Server  2000 - 8.00.760 (Intel X86)

This works on MSSQL:

SELECT distinct ChartID, City, FacilityStatus, ID, State ,case when ID is null then 9999 else ID end as SortID 
FROM tblGeneral
WHERE tblGeneral.FacilityStatus= 'Open' 
ORDER BY SortID ASC, City ASC 

Select allOpen in new window

See this working example on SQLFiddle

Re the UNION thing, yes I realised as soon as I posted and edited my post :(
After your last comment, I'm still not sure which SQL backend you want to use this query on? If you're executing this in an Access front end against a linked MSSQL backend, you could use the above syntax in a passthru query, or use a column defined as "SortID: NZ(ID,999999)" formula in the Access query designer and sort on that column.

I have never used CASE before. Would this be the correct punctuation?
With this code it is not giving me any options

sql = "SELECT distinct ChartID, City, FacilityStatus, ID, State, case when ID is null then 9999 else ID end as SortID " & _
			"FROM tblGeneral " & _ 
			"WHERE tblGeneral.FacilityStatus= 'Open' " & _
			"ORDER BY SortID ASC, City ASC "

Select allOpen in new window

I need to know why you're building this as dynamic SQL rather than in the query designer. Are you running this against a linked table on your MSSQL backend? If so, and NOT using a passthru query, you would have to rewrite using Access flavour of SQL. The Jet database engine doesn't support the CASE construct, but you can use the NZ(value to test, value if null) function.

SELECT distinct ChartID, City, FacilityStatus, ID, State ,NZ(ID,99999) as SortID 
FROM tblGeneral
WHERE tblGeneral.FacilityStatus= 'Open' 
ORDER BY SortID ASC, City ASC 

Select allOpen in new window

I'd suggest you try it in the query designer in Access first. I don't have Access in front of me at the moment but this should work.

This link to Jeff Smith's SQL Server blog (Access/TSQL cheatsheet) is very useful for finding equivalent commands to work with either system.

That reminded me that the MSSQL version can be written more compactly with the COALESCE function in place of the CASE function.

SELECT distinct ChartID, City, FacilityStatus, ID, State,coalesce(id,999999) as SortID
FROM tblGeneral
WHERE tblGeneral.FacilityStatus= 'Open' 
ORDER BY coalesce(id,999999) ASC, City ASC 

Select allOpen in new window

This SQLFiddle example demonstrates that the revised MS SQL Server version works.

If you didn't have the DISTINCT keyword in your query it wouldn't be necessary to include the SortID column in the query results (you could just sort on it) but DISTINCT requires that terms in the ORDER BY clause are contained in the select list.

I see nothing in MSAccess to allow me to sort with null records at the end.

My data is coming from the MSAccess database. My page is .asp.
Using COALESCE negates the display of any of my options.

Any other suggestions?

Hi Malloy,

This is the code for MSAccess. I've tested it on Access 2010 and can confirm that it works. It doesn't use coalesce, but uses the NZ function to replace null IDs with a value of your choice. I've used 99999, but you may need to use a larger number (one that will definitely be larger than your highest ID).

SELECT distinct ChartID, City, FacilityStatus, ID, State ,NZ(ID,99999) as SortID 
FROM tblGeneral
WHERE tblGeneral.FacilityStatus= 'Open' 
ORDER BY NZ(ID,99999) ASC, City ASC 

Select allOpen in new window

Hi Simon.

It may work correctly in MSAccess, but when I add the code to my SQL statement on the ASP page and try to display on the web, there are NO results displayed.

<%
		Dim ChartID, City, ID, State

		Response.Write ("<select name = 'ChartID'>")
		Response.Write ("<option value=''>-- Click for list of Locations --</option>")
		
		Set objRS = Server.CreateObject("ADODB.Recordset")


		sql = "SELECT DISTINCT ChartID, City, FacilityStatus, ID, State, NZ(ID,99999) as SortID " & _
			"FROM tblGeneral " & _ 
			"WHERE tblGeneral.FacilityStatus= 'Open' " & _
			"ORDER BY NZ(ID,99999) ASC, City ASC "
			objRS.Open sql, objConn



		While Not objRS.EOF
			ChartID = objRS("ChartID")
			City = objRS("City")
			State = objRS("State")
			ID = objRS("ID")
			Response.Write "<option value = '" & ChartID & "'>"
			Response.Write "VISN " & ID & " -- " & city & ", " & State & "</option>"
			objRS.MoveNext
		Wend
		Response.Write ("</select>")
		objRS.Close
		%>

Select allOpen in new window