• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

ORDER BY sort with Null/Empty field last

This should be simple, but I have tried multiple recommendations and nothing seems to work.

I want to sort by a numeric field and then within there by City both in ascending order but with the records with an empty/null field (ID) at the end.

sql = "SELECT DISTINCT ChartID, City, FacilityStatus, ID, State FROM tblGeneral " & _ 
"WHERE tblGeneral.FacilityStatus= 'Open' " & _
"ORDER BY ID ASC NULL LAST, City ASC "
				

Open in new window

0
Malloy1446
Asked:
Malloy1446
  • 5
  • 5
1 Solution
 
SimonCommented:
What flavour of SQL backend? MSSQL, MySQL, Oracle or another, such as an MS Access or other file?
0
 
Malloy1446Author Commented:
Tried the union. It completely deleted all my options.
0
 
Malloy1446Author Commented:
Database:  MS Access
Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
SimonCommented:
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 

Open 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.
0
 
Malloy1446Author Commented:
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 "

Open in new window

0
 
SimonCommented:
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 

Open 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 

Open 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.
0
 
Malloy1446Author Commented:
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?
0
 
SimonCommented:
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 

Open in new window

0
 
Malloy1446Author Commented:
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
		%>

Open in new window

0
 
SimonCommented:
Hi Malloy, Sorry, it's 10 years since I ran an ASP website with an Access database :(.
I'm thinking that the NZ function is not supported in this context... and this link confirms it.

I'm surprised you're not getting an 'unsupported' error.

You can try
		sql = "SELECT DISTINCT ChartID, City, FacilityStatus, ID, State, IIF(ISNULL([ID]), 999999, [ID])  " & _
			"FROM tblGeneral " & _ 
			"WHERE tblGeneral.FacilityStatus= 'Open' " & _
			"ORDER BY IIF(ISNULL([ID]), 999999, [ID])  ASC, City ASC "
			objRS.Open sql, objConn

Open in new window


Be aware that this type of query is not going to perform well - i.e. will be slow if you have a large dataset or a lot of users. Try it and see, but you might want to consider updating the table first to replace nulls with 999999 or some value, so that you don't have to order your query by a computed column.

This ASP FAQ site has lots of relevant information on the limitations of Access as a web backend.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now