Ordering by a Column that's not in the Group By or Enapsulated in an Aggregate

Question

I have a problem getting this JPA query to work on MS SQL Server 2008.

The background is as follows: Users create jobs for clients, of which there are many. I am displaying a list of his most recently used clients to the user to make the selection easier.

SELECT DISTINCT c FROM Client c 
    JOIN c.jobs j 
    WHERE j.user = ?1 
    ORDER BY j.created DESC

The query works just fine - using MySQL. MS SQL Server (2008) complains that I cannot sort by j.created because it is not part of the select list. This is the error message:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I can't seem to find an elegant workaround for this limitation. Does anyone have an idea?

Answer 1

Because you are selecting a distinct c from many, Sql server won't know which j.created to order by. You can group by c and then use an aggregate to order by the min or max j.created. Something like...

SELECT c.col1, c.col2 
FROM Client c 
JOIN c.jobs j 
WHERE j.user = ?1 
Group by c.col1, c.col2
ORDER BY MIN(j.created) DESC

Answer 2

This isn't a limitation of SQL. The problem is that the sort doesn't make sense in terms of your query. The Distinct operator is going to aggregate multiple values of C each of which might have a different create date. So in this situation how would SQL sort it?

C    Create  
1    1/1/2009  
2    3/1/2008  
1    12/2/2009  
9    4/1/2009

Answer 3

Duckworth's answer pretty much solved the problem, so this is only for reference.

This was my first attempt after reading duck's and John's answers:

SELECT c FROM Client c JOIN c.jobs j 
    WHERE j.user = ?1 
    GROUP BY c 
    ORDER BY MIN(j.created) DESC

But selecting the full entites directly does not play with the GROUP BY clause, and results in a SQLGrammarException.

Instead, I now get only the IDs using this query:

SELECT c.id FROM Client c JOIN c.jobs j 
    WHERE j.user = ?1 
    GROUP BY c.id 
    ORDER BY MIN(j.created) DESC

Then I only have to fetch the entities using the IDs.

Thanks, everybody!