sql-serversql-server-2008t-sqlstored-procedures

Select most recent record for each member


Using T-SQL I have 2 tables one which lists all members(recEntrants). The second table(recEntrantStatus) contains status update for each member. At present I have the following SQL to retrieve all updates for all members.

SELECT EN.Id, EN.artistName, GR.genre, ES.lastModifiedOn, EN.voteStatus, 
ES.notified
FROM recEntrantStatus AS ES
JOIN recEntrants AS EN
ON ES.entrantId = EN.Id
JOIN recGenre AS GR
ON EN.genreId = GR.Id
AND ES.judgeId = @judgeId
AND ES.roundId > 0
ORDER BY ES.voted DESC, ES.roundId, EN.Id

Update

New requirements added below:

SELECT EN.Id, EN.artistName, GR.genre, ES.lastModifiedOn, EN.voteStatus, 
ES.notified
FROM recEntrantStatus AS ES
LEFT JOIN recEntrants AS EN
ON ES.entrantId = EN.Id
LEFT JOIN recGenre AS GR
ON EN.genreId = GR.Id
WHERE ES.roundId = 2

However, what I need to achieve is to just pull the most recent status update/record for each member.

P.s. I have a modifiedDate column on recEntrantStatus.


Solution

  • Using row_number() over (partition by ES.entrantId order by ES.lastModifiedOn desc). Please add Order by column list to the sub query if you still need to use the same order. Also if you need to select records who has NO status records then use a LEFT JOIN instead of a JOIN

    SELECT * FROM (
        SELECT EN.Id, EN.artistName, GR.genre, ES.lastModifiedOn, EN.voteStatus, 
           ES.notified,
           row_number() over (partition by ES.entrantId order by ES.lastModifiedOn desc) rn 
        FROM recEntrantStatus AS ES
        JOIN recEntrants AS EN
            ON ES.entrantId = EN.Id JOIN recGenre AS GR
            ON EN.genreId = GR.Id AND ES.judgeId = @judgeId AND ES.roundId > 0
    ) A
    WHERE A.rn = 1
    --ORDER BY A.voted DESC, A.roundId, A.Id
    

    EDIT (as per OP edit):

    SELECT * FROM (
        SELECT ES.entrantId Id, EN.artistName, GR.genre, ES.lastModifiedOn, EN.voteStatus, 
           ES.notified,
           row_number() over (partition by ES.entrantId order by ES.lastModifiedOn desc) rn 
        FROM recEntrantStatus AS ES
        LEFT JOIN recEntrants AS EN
            ON ES.entrantId = EN.Id LEFT JOIN recGenre AS GR
            ON EN.genreId = GR.Id 
        --AND ES.judgeId = @judgeId 
        WHERE ES.roundId = 2
    ) A
    WHERE A.rn = 1