Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 127
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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