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

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 a开发者_StackOverflow中文版re 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?

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

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  

May be this question is a bit old but I have found a easier workaround.

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

Adding the fetch keyword to the join with tell JPA to include fields in the join in the select statement so mssql would no longer complaint about it.

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!