How do I join the most recent row in one table to another table ?

How do I join the most recent row in one table to another table ?

Asked on December 27, 2018 in SQL Server.
Add Comment


  • 5 Answer(s)

    Here is the solution,

    SELECT e.*, s1.score, s1.date_added
    FROM entities e
      INNER JOIN scores s1
        ON (e.id = s1.entity_id)
      LEFT OUTER JOIN scores s2
        ON (e.id = s2.entity_id AND s1.id < s2.id)
    WHERE s2.id IS NULL;
    
    Answered on December 27, 2018.
    Add Comment

    To get the solution, add this snippet in the code,

    SELECT e.*, s1.score
    FROM entities e
    INNER JOIN score s1 ON e.id = s1.entity_id
    WHERE NOT EXISTS (
        SELECT 1 FROM score s2 WHERE s2.id > s1.id
    )
    
    Answered on December 27, 2018.
    Add Comment

    Try to do this,

    SELECT entities.*,
           scores.score,
           scores.date_added
    FROM entities
     
    INNER JOIN scores
    ON entities.id = scores.entity_id
     
    WHERE scores.date_added =
      (SELECT max(date_added) FROM scores where entity_id = entities.id)
    
    Answered on December 27, 2018.
    Add Comment
    SELECT entities.*, 
           scores.score, 
           scores.date_added 
    FROM entities
    
    INNER  JOIN scores
    ON entities.id = scores.entity_id
    
    inner join 
        (
        SELECT 
               entity_id, max(date_added) as recent_date
        FROM scores
        group by entity_id
        ) as y on entities.id = y.entity_id and scores.date_added = y.recent_date
    SELECT entities.*, 
           scores.score, 
           scores.date_added 
    FROM entities
    
    INNER  JOIN scores
    ON entities.id = scores.entity_id
    
    inner join 
        (
        SELECT 
               entity_id, max(date_added) as recent_date
        FROM scores
        group by entity_id
        ) as y on entities.id = y.entity_id and scores.date_added = y.recent_date
    Answered on January 13, 2019.
    Add Comment
    SELECT entities.*, 
           scores.score, 
           scores.date_added 
    FROM entities
    
    INNER  JOIN scores
    ON entities.id = scores.entity_id
    
    WHERE entities.id in 
    (select id from scores s2 where date_added = max(date_added) and s2.id = entities.id)
    
    ORDER BY scores.date_added DESC
    LIMIT 1
    Answered on January 13, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.