Mysql – Join 2 tables and get the latest record

MySQL

I have 2 tables table1 contains the columns of id, status,created_by,created_date,name and table2 is a log table which contains id, updated_by,updated_date. Table2 contains multiple records of same id. I'm trying to get the resultant query which support to get the selected columns from both tables by join operation. but when I tried i'm getting the duplicate records associated with that id. I want only one latest record for that id. My query…

select  t.created_by,t.created_date,tl.updated_by,tl.updated_date
    from  test_name t
    join (
        SELECT  updated_by,updated_date,id
            from  test_log
            order by  updated_date desc
            limit  1
         ) tl  ON t.id=tl.id
    where  state = 'active';

Best Answer

You could return the sequential number of a row within a partition (in this case id) of a result set, starting at 1 (AND seqnum = 1) for the first row in each partition.

See below

SELECT t.created_by, 
       t.created_date, 
       tl.updated_by, 
       tl.updated_date 
FROM   test_name t 
      INNER JOIN (SELECT tl.*,
            Row_number() 
              OVER ( 
                partition BY id 
                ORDER BY updated_date DESC ) AS seqnum 
             FROM   test_log tl) tl 
         ON t.id = tl.id 
            AND seqnum = 1 
WHERE  state = 'active';
Related Question