Fix: "Field references in ORDER BY clause must appear in the select list if SELECT DISTINCT is specified."

After upgrading a Java batch application with a Progress/OpenEdge back end to use a new JDBC driver version this morning, while testing the upgraded application to check for any issues with using the new driver, the app generated a SQL error: "[DataDirect][OpenEdge JDBC Driver][OpenEdge] Field references in ORDER BY clause must appear in the select list if SELECT DISTINCT is specified. (13637)".

I googled for that error message and the search didn't come up with any results, so I figured I'd blog the solution briefly here. It turns out the error message in this case is actually pretty good, as SQL datasource error messages go. Given a query that involves a SELECT DISTINCT, a JOIN, and a ORDER BY, like this one:

SELECT DISTINCT a.customer_id
 FROM users a
 INNER JOIN sales b  
 ON a.customer_id = b.customer_id
 ORDER BY b.customer_id 

The problem is that the field reference in the SELECT clause and the reference in the ORDER BY clause don't match. In this case, the SELECT clause references table "a" and the ORDER BY clause references table "b".

The fix is to change the query such that both references are the same:

SELECT DISTINCT a.customer_id
 FROM users a
 INNER JOIN sales b  
 ON a.customer_id = b.customer_id
 ORDER BY a.customer_id 

To double-check my example, I tested the first simple query above briefly in Microsoft Access, and it turns out that Access doesn't like the field mismatch either. The Access error message was "ORDER BY clause (...) conflicts with DISTINCT."