Solved

ORDER BY sort with Null/Empty field last

Posted on 2014-12-05
10
119 Views
Last Modified: 2014-12-10
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
Comment
Question by:Malloy1446
  • 5
  • 5
10 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40483960
What flavour of SQL backend? MSSQL, MySQL, Oracle or another, such as an MS Access or other file?
0
 

Author Comment

by:Malloy1446
ID: 40483976
Tried the union. It completely deleted all my options.
0
 

Author Comment

by:Malloy1446
ID: 40483978
Database:  MS Access
Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 18

Expert Comment

by:Simon
ID: 40483985
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
 

Author Comment

by:Malloy1446
ID: 40484003
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
 
LVL 18

Expert Comment

by:Simon
ID: 40484017
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
 

Author Comment

by:Malloy1446
ID: 40487647
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
 
LVL 18

Expert Comment

by:Simon
ID: 40487685
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
 

Author Comment

by:Malloy1446
ID: 40487849
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
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40487882
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
Building a website can seem like a daunting task to the uninitiated but it really only requires knowledge of two basic languages: HTML and CSS.
In this tutorial viewers will learn how add a scalable full-width header using CSS3. Create a new HTML document with an internal stylesheet. Set a tiled background.:  Create a new div and name it Header. Position it with position:absolute at the top…
In this tutorial viewers will learn how to embed videos in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <video> tag to insert a video. Define the src as the URL of your video; this is similar to …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question