Retrive distinct record based on ID order by Date

Question

I've a query :

select C.ChapterID, C.ChapterName, TA.TestAllotmentID, 
       T.TestName, S.StudentFname, B.BatchName, TA.UpdatedDate 
from TransTestAllotment TA,
     MstStudent S,
     MstBatchDetails B,
     MstTest T,
     MstChapter C
where TA.StudentID = 47
  and TA.BatchID = 10 
  and T.TestID = TA.TestID
  and S.StudentID = TA.StudentID
  and B.BatchID = TA.BatchID
  and T.ChapterID = C.ChapterID
  and TA.IsAttempted = 'True'
  and TA.IsEvaluated = 'True'
order by TA.UpdatedDate desc

It returns result as below.

+-----------+-----------------------+-----------------+-------------------------+
| ChapterID |      ChapterName      | TestAllotmentID |       UpdatedDate       |
+-----------+-----------------------+-----------------+-------------------------+
|        52 | HTML Basics - Part II |              37 | 2016-03-14 13:12:53.000 |
|        52 | HTML Basics - Part II |              36 | 2016-03-14 13:11:59.000 |
|        52 | HTML Basics - Part II |              35 | 2016-03-14 13:11:35.000 |
|        50 | HTML Basics - Part I  |              28 | 2016-03-11 18:45:51.000 |
|        50 | HTML Basics - Part I  |              27 | 2016-03-11 18:45:05.000 |
|        50 | HTML Basics - Part I  |              26 | 2016-03-11 18:43:48.000 |
|        50 | HTML Basics - Part I  |              24 | 2016-03-11 18:42:37.000 |
|        50 | HTML Basics - Part I  |              22 | 2016-03-11 18:41:07.000 |
+-----------+-----------------------+-----------------+-------------------------+

Now I want to retrieve distinct record based on chapter ID order by Updated date. The final output that I am expecting is:

+-----------+-----------------------+-----------------+-------------------------+
| ChapterID |      ChapterName      | TestAllotmentID |       UpdatedDate       |
+-----------+-----------------------+-----------------+-------------------------+
|        52 | HTML Basics - Part II |              37 | 2016-03-14 13:12:53.000 |
|        50 | HTML Basics - Part I  |              28 | 2016-03-11 18:45:51.000 |
+-----------+-----------------------+-----------------+-------------------------+

How do I write a SQL query to get this output?

`select chapterID,chapterName,max(testAllotmentID),max(updatedDate) from resultTbl group by chapterID,chapterName` ?? — Zahiro Mor, Mar 14 '16 at 10:06
Still struggling? Consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Mar 14 '16 at 10:11
@ArifulIslam, the original query returns no duplicates, so `SELECT DISTINCT` will not make any change. — jarlh, Mar 14 '16 at 10:11
Tip of today: Switch top modern, explicit `JOIN` syntax! Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh, Mar 14 '16 at 10:13
Possible duplicate of [SELECT query return 1 row from each group](http://stackoverflow.com/questions/14375099/select-query-return-1-row-from-each-group) — Shadow, Mar 14 '16 at 10:17
@ZahiroMor - select chapterID,chapterName,max(testAllotmentID),max(updatedDate) from resultTbl group by chapterID,chapterName query works. But not exactly for my scenario as the testallotmentid need not be the one which is max. — Ranjitha Hegde R, Mar 14 '16 at 10:36
There are two areas you need to research. Read up on the [GROUP BY](https://msdn.microsoft.com/en-us/library/ms177673.aspx) clause and [AGGREGATE FUNCTIONS](https://msdn.microsoft.com/en-GB/library/ms173454.aspx). This will solve your problem. — David Rushton, Mar 14 '16 at 10:36
I want to select top one record of each ChapterID based on updated date Desc. — Ranjitha Hegde R, Mar 14 '16 at 10:54
SELECT Top 1 x.* from (select C.ChapterID,C.ChapterName,TA.TestAllotmentID, T.TestName,S.StudentFname,B.BatchName,TA.UpdatedDate from TransTestAllotment TA, MstStudent S, MstBatchDetails B, MstTest T,MstChapter C where TA.StudentID=47 and TA.BatchID=10 and T.TestID=TA.TestID and S.StudentID=TA.StudentID and B.BatchID=TA.BatchID and T.ChapterID=C.ChapterID and TA.IsAttempted='True' and TA.IsEvaluated='True' and C.chapterID=52) x order by x.updatedDate Desc — Ranjitha Hegde R, Mar 14 '16 at 10:54
The above query gives me my desired result. But this query I need to write separately for every chapterid. So if there are 10 chapters. The I need to write it 10 times. — Ranjitha Hegde R, Mar 14 '16 at 10:55

score 2 · Accepted Answer · answered Mar 14 '16 at 11:01

You should fix your query to use proper explicit JOIN syntax. But the answer to your question is to use window functions:

with q as (
      <your query here>
     )
select q.*
from (select q.*,
             row_number() over (partition by chapterid order by updateddate desc) as seqnum
      from q
     ) q
where seqnum = 1;

score 2 · Answer 2 · answered Mar 14 '16 at 12:40

Thank you Gordon Linoff. My final query is as below.

Select Y.* from (select X.*, row_number() over (partition by chapterid order by updateddate desc) as SequencNo from 
    (Select C.ChapterID,C.ChapterName,TA.TestAllotmentID, T.TestName,
    S.StudentFname,B.BatchName,TA.UpdatedDate
    from TransTestAllotment TA, MstStudent S, MstBatchDetails B, 
    MstTest T,MstChapter C 
    where TA.StudentID=47 and TA.BatchID=10 and 
    T.TestID=TA.TestID and S.StudentID=TA.StudentID and 
    B.BatchID=TA.BatchID and T.ChapterID=C.ChapterID and 
    TA.IsAttempted='True' and TA.IsEvaluated='True') X) Y Where SequencNo=1

score 0 · Answer 3 · answered Mar 14 '16 at 10:24

I suggest to reshape the query using JOIN and then see if you need further clauses:

This is a try

SELECT 
C.ChapterID, 
C.ChapterName,
FROM 
(SELECT
TA.TestAllotmentID, 
T.TestName, 
S.StudentFname, 
B.BatchName, 
TA.UpdatedDate 
FROM TransTestAllotment TA
LEFT JOIN MstStudent S ON S.StudentID=TA.StudentID
LEFT JOIN MstBatchDetails B ON B.BatchID=TA.BatchID
LEFT JOIN MstTest T ON T.TestID=TA.TestID
WHERE TA.StudentID=47
AND TA.BatchID=10 as T1
AND TA.IsAttempted='True'
AND TA.IsEvaluated='True'
ORDER BY TA.UpdatedDate desc)
LEFT JOIN MstChapter C ON  T1.ChapterID=C.ChapterID;

Regards

Its not working. There were a couple of syntax errors. Chapter ID is not included in sub table T1. When I added it, again in it gave a same result as my 1st query result. — Ranjitha Hegde R, Mar 14 '16 at 10:32

score 0 · Answer 4 · answered Mar 14 '16 at 12:44

can you try this query :

select C.ChapterID, C.ChapterName, TA.TestAllotmentID, TA.UpdatedDate 
    from TransTestAllotment TA,
         MstStudent S,
         MstBatchDetails B,
         MstTest T,
         MstChapter C
    where TA.StudentID = 47
      and TA.BatchID = 10 
      and T.TestID = TA.TestID
      and S.StudentID = TA.StudentID
      and B.BatchID = TA.BatchID
      and T.ChapterID = C.ChapterID
      and TA.IsAttempted = 'True'
      and TA.IsEvaluated = 'True'
     group by  C.ChapterID, C.ChapterName, TA.TestAllotmentID, TA.UpdatedDate  
     having  (C.ChapterID,TA.TestAllotmentID) in 
     (select C1.ChapterID ,max(TA1.TestAllotmentID)  
        from TransTestAllotment TA1, MstTest T1, MstChapter C1 
        where  T1.TestID = TA1.TestID and T1.ChapterID = C1.ChapterID)
     order by TA.UpdatedDate desc

Retrive distinct record based on ID order by Date

4 Answers4