Fetching latest records of individual by joining 2 tables

Question

I have to fetch the latest record of the student which derived by joining 2 tables:

table 1:      table 2:

id name       id  marks    EXAM attended time        status
--------      ----------------------------------------------
1  ABC         1   90     2019-04-05 06:00:00          PASS 
2  DEF         1   25     2018-06-05 08:00:00          FAIL 
               2   45     2019-03-05 06:00:00          FAIL
               2   22     2019-01-05 09:00:00          FAIL

On joining both tables I got this:

#  name    marks   EXAM ATTENDED TIME         status 
------------------------------------------------------
1   ABC     90     2019-04-05 06:00:00          PASS    
2   ABC     25     2018-06-05 08:00:00          FAIL 
3   DEF     45     2019-03-05 06:00:00          FAIL   
4   DEF     22     2019-01-05 09:00:00          FAIL
5   DEF     55     2019-04-05 09:00:00          PASS
6   DEF     66     2019-05-05 09:00:00          PASS
7   DEF     99     2018-05-05 09:00:00          PASS

I want to fetch the latest result on datetime and name.

The output I need is:

id  name    marks   EXAM ATTENDED TIME         status
------------------------------------------------------
1   ABC     90     2019-04-05 06:00:00          PASS 
6   DEF     66     2019-05-05 09:00:00          PASS

Please include your current query. Your output does not seem to match the two input tables. — Tim Biegeleisen, Jun 24 '19 at 11:03

score 1 · Answer 1 · answered Jun 24 '19 at 11:04

You can try below using correlated subquery

select * from table1 a1
inner join table2 a on a1.id=a.id
where  exam_attended_time in (select max(exam_attended_time) from table2 b where a.id=b.id)

OR you can use row_number() if your db supports it-

select * from 
(
select a.name,a1.*,row_number(partition by a.id order by exam_attended_time desc)rn from table1 a1
    inner join table2 a on a1.id=a.id
)X where rn=1

score 1 · Answer 2 · answered Jun 24 '19 at 11:48

You could use a window function (ROW_NUMBER).

SELECT 
        x.id
    ,   x.NAME
    ,   x.marks
    ,   x.ExamAttendTime
    ,   x.status 
FROM 
    (
    SELECT 
            t1.id
        ,   t1.NAME
        ,   t2.marks
        ,   t2.ExamAttendTime
        ,   t2.status
        ,   ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.ExamAttendTime DESC) AS ROWNUMBER
    FROM 
    dbo.Table1 t1 
    JOIN dbo.Table2 t2 ON t2.id = t1.id
    ) x
WHERE
    x.ROWNUMBER = 1

score 0 · Answer 3 · answered Jun 24 '19 at 11:29

i don't know how you fetch the record like marks '99' and '66' and EXAM attended time '2019-05-05 09:00:00 ' which is not available in table itself.

though this will might help you on getting correct data .

select a.id,a.name,b.marks,b.[EXAM attended time],b.[status] from table 1 a 
join table 2 b on a.id=b.id where [EXAM attended time] in 
(select max([EXAM attended time])[EXAM attended time]from exam group by id)

Shushil Bohara · Answer 4 · 2019-06-24T11:37:29.173

0

IF you are using SQL SERVER then you can use TOP as below to fetch latest records

SELECT A.id, 
    A.name, 
    B.marks, 
    B.EXAM_attended_time, 
    B.Status
FROM table1 A
OUTER APPLY (SELECT TOP 1 *
            FROM table2 B WHERE B.id = A.id
            ORDER BY B.EXAM_attended_time DESC) B 
WHERE B.ID = A.id

edited Jun 24 '19 at 11:37

answered Jun 24 '19 at 11:31

Shushil Bohara

5,556
2
15
32

Fetching latest records of individual by joining 2 tables

4 Answers4