tag:blogger.com,1999:blog-495177919198719500.post556993521450648769..comments2023-12-27T20:52:29.483-08:00Comments on Microsoft Sql Server Tutorials: Select the Latest Record based on a Condition in SQL ServerSuprotim Agarwalhttp://www.blogger.com/profile/08349831623922214390noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-495177919198719500.post-5873435891469335322010-06-11T11:42:23.190-07:002010-06-11T11:42:23.190-07:00SELECT a.CandID, a.CourseID, a.DateOfExam
FROM @TT...SELECT a.CandID, a.CourseID, a.DateOfExam<br />FROM @TT a<br />WHERE a.DateOfExam = <br /> (SELECT MAX(b.DateOfExam)<br /> FROM @TT b<br /> WHERE a.CandID = b.CandID AND a.CourseID = b.CourseID)<br />ORDER BY a.CandID, a.CourseID<br /><br />is another way to quickly select the latest or MAX date. For your test count query: <br /><br />SELECT CourseID, COUNT(CourseID) AS TestCnt<br />FROM @TT<br />GROUP BY CourseID<br />ORDER BY CourseID<br /><br />-- BillAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-89726751145808517672010-05-15T02:18:54.205-07:002010-05-15T02:18:54.205-07:00Very Informative. What if there is no such restric...Very Informative. What if there is no such restriction of 2 exams per course and instead i want to keep a count of how many times an exam was taken for each course?<br /><br />-- MazzAnonymousnoreply@blogger.com