May 22, 2013 at 5:17 am
Try the below query..
SELECT *
FROM @mySampleTable s
OUTER APPLY (SELECT TOP 1 FROM @mySampleTable g WHERE s.CaseID = g.CaseID
AND s.StudentId = g.StudentID ORDER BY CreatedDate DESC) h
May 22, 2013 at 5:27 am
shettybhas (5/22/2013)
Try the below query..SELECT *
FROM @mySampleTable s
OUTER APPLY (SELECT TOP 1 CaseID FROM @mySampleTable g WHERE s.CaseID = g.CaseID
ORDER BY CreatedDate DESC) h
This will give you lastest modified CaseID, and outerapply make qurery runs faster.
There is nothing in this query enforces order of records returned from @mySampleTable. So, records will be returned in random order.
Also, it doesn't perform required filtering. It will return all rows in a random order.
Basically the above query is exactly the same as simple:
SELECT * FROM @mySampleTable
Some redundant OUTER APPLY - nothing else.
Actually, have you compared its execution plan with INNER JOIN version? I'm in great doubt that it will be any faster...
May 22, 2013 at 6:18 am
Sorry.. I didnt read the post completely, so here is the modified query,
SET STATISTICS PROFILE ON
SELECT aa.* FROM @mySampleTable aa
INNER JOIN (SELECT a.CaseId, a.latestUpd FROM (SELECT CASEID, COUNT(*) CCnt, MAX(CreatedDate) LatestUpd FROM @mySampleTable GROUP BY CASEID) A OUTER APPLY (SELECT TOP 1 CASEID, STUDENTID, COUNT(*) SCnt FROM @mySampleTable B WHERE A.cASEiD = B.cASEiD GROUP BY CASEID, STUDENTID) AS BWHERE A.CCNT <> b.scnt) bb on aa.CaseId = bb.CaseId
ORDER BY latestUpd desc, aa.CaseId
I tried to avoid using Max and Min function on Char column as this may back fire with huge data. and used Outer Apply instead with Counting No Of Rows against each CaseID and StudentId
May 22, 2013 at 6:44 am
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.
May 22, 2013 at 8:32 am
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
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply