geoff5 (5/22/2013)
Eugene,After reading your explanation of the reason why GROUP BY is more efficient than window functions, I can see the logic in your in-line view solution. I would still do it as a CTE, but that is merely a matter of refactoring what is essentially the same query logic. I do believe your last complete solution is the best approach.
Actually, I don't have anything against CTE, and will always use it over sub-query. It makes query easier to read and, therefore, maintain. So, if you insist on using CTE in this case I would recommend that version:
;WITH cte
AS
(
SELECT CaseID, MAX(CreatedDate) LatestUpdate
FROM @mySampleTable
GROUP BY CaseID
HAVING MAX(StudentID) != MIN(StudentID)
)
SELECT s.*
FROM @mySampleTable AS s
JOIN cte AS fs ON fs.CaseID = s.CaseID
ORDER BY fs.LatestUpdate DESC,
s.CaseID,
s.CreatedDate DESC -- here it's depends how you want to sort records for the same CaseId