SQL to get max date with group by

Question

I have a transaction table something like this ...

-------------------------------------------------------------------------------------
Subject_id | Question_id | Answerd_id | Student_id | Answer_text | Insert_DTM
-------------------------------------------------------------------------------------
5005       | 3004        | 1004       | 1309       | test        | 2018-05-31 12:07:42
-------------------------------------------------------------------------------------
5005       | 3005        | 1005       | 1309       | test        | 2018-05-31 12:07:42
-------------------------------------------------------------------------------------
5005       | 3004        | NULL       | 1309       | Null        | 2018-05-31 12:09:43
-------------------------------------------------------------------------------------
5005       | 3002        | NULL       | 1309       | Null        | 2018-05-31 12:07:42
-------------------------------------------------------------------------------------
5005       | 3005        | 1005       | 1309       | test        | 2018-05-31 11:35:47
-------------------------------------------------------------------------------------
5005       | 3005        | 1005       | 1309       |             |2018-05-31 11:36:37

From this table I have to find out most recent answered row for a student and subject id.

The SQL I am using ..

SELECT subject_id, 
       question_id, 
       answer_id, 
       student_id, 
       answer_text, 
       insert_dtm 
FROM   exam_trans 
WHERE  student_id = 1309 
       AND subject_id = 5005 
       AND insert_dtm IN (SELECT Max(insert_dtm) 
                          FROM   exam_trans 
                          WHERE  student_id = 1309 
                                 AND subject_id = 5005 
                          GROUP  BY question_id) 
GROUP  BY subject_id, 
          question_id, 
          answer_id, 
          member_id, 
          answer_text, 
          insert_dtm 
ORDER  BY answer_id, 
          question_id DESC

But As output I am getting

-------------------------------------------------------------------------------------
Subject_id | Question_id | Answerd_id | Student_id | Answer_text | Insert_DTM
-------------------------------------------------------------------------------------
5005       | 3004        | 1004       | 1309       | test        | 2018-05-31 12:07:42
-------------------------------------------------------------------------------------
5005       | 3005        | 1005       | 1309       | test        | 2018-05-31 12:07:42
---------------------------------------------------------------------------------------
5005       | 3004        | NULL       | 1309       | Null        | 2018-05-31 12:09:43
--------------------------------------------------------------------------------------
5005       | 3002        | NULL       | 1309       | Null        | 2018-05-31 12:07:42

3004 is appearing twice in the output , but my expected output is

Subject_id | Question_id | Answerd_id | Student_id | Answer_text | Insert_DTM
-------------------------------------------------------------------------------------
5005       | 3005        | 1005       | 1309       | test        | 2018-05-31 12:07:42
---------------------------------------------------------------------------------------
5005       | 3004        | NULL       | 1309       | Null        | 2018-05-31 12:09:43
--------------------------------------------------------------------------------------
5005       | 3002        | NULL       | 1309       | Null        | 2018-05-31 12:07:42

3004 should come once with recent timestamp only....

Can any one help me out with the proper SQL...

I am using Oracle RDS in AWS

how come `Subjectid = 5005` has two answer at the same timestamp? and why you choose one over the other.? — Juan Carlos Oropeza, May 31 '18 at 16:24
Check out window functions. https://oracle-base.com/articles/misc/analytic-functions — Shawn, May 31 '18 at 16:40
@JuanCarlosOropeza, Thanks for your reply ... Thats the business logic , and "how come" is not the main botheration in this this post , question is how I can get the expected output with this table data.... — Biswajit, May 31 '18 at 16:55
"how come" is the main issue. If the business logic isnt clear then we cant do the proper query — Juan Carlos Oropeza, Jun 01 '18 at 18:05
@JuanCarlosOropeza... yes thats true , but not in this context , if you can understand .... any ways I got the resolution , so please be kind enough to ignore this thread.... Thanks a lot for your time and option... — Biswajit, Jun 07 '18 at 04:53

score 2 · Answer 1 · answered May 31 '18 at 16:27

for this is better use ROW_NUMBER()

SELECT *
FROM (SELECT *,
             ROW_NUMBER() OVER (PARTION BY Question_id, Subject_id
                                ORDER BY Insert_DTM DESC) as rn
      FROM exam_trans 
      WHERE  student_id = 1309 
        AND  subject_id = 5005 
     )
WHERE rn = 1

score 1 · Answer 2 · answered May 31 '18 at 16:25

You appears to want :

select e.*
from exam_trans e
where Insert_DTM = (select max(e1.Insert_DTM)
                    from  exam_trans e1
                    where e1.Subject_id = e.Subject_id and
                          e1.Question_id = e.Question_id
                   );

score 0 · Answer 3 · answered May 31 '18 at 16:27

0

You can use sub-query and row_number().

https://stackoverflow.com/a/10515397/7328059 should answer your question.

answered May 31 '18 at 16:27

xy2589

1
1

SQL to get max date with group by

3 Answers3