Solved

ORDER BY sort with Null/Empty field last

Posted on 2014-12-05
10
122 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
Display Flex does not Wrap Items 2 46
T-SQL: Only Wanting One Record 8 61
Pass a js value to an html form 5 42
body or div size relative to background image 7 10
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.
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
In this Micro Tutorial viewers will learn how to create navigation buttons that change on rollover, using CSS (Continuation of the CSS Image Sprite tutorial) Create a parent ID for all the list items       - Specify position: absolute and display: block…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

738 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