How to insert select top 1 into my query?

Question

I have a table Candidates. Each candidate have a manager. And each candidate have status from 1 to 11, which is stored in table CandidatesStatusesLog. I need to understand, how much of each status have each manager. Query below retun all statuses from 1 to 11 for each candidate, but I only need to get the last one.

SELECT COUNT(c.candidateId) as resumesCount,
csl.[statusId],
s.[managerINN]
FROM sm.Candidates c
INNER JOIN sm.CandidateVacancies cv on c.[candidateId] = cv.[candidateId]
INNER JOIN sm.Staff s on s.[staffId] = cv.[vacancyId]
INNER JOIN sm.CandidatesStatusesLog csl on c.[candidateId] = csl.[candidateId]
GROUP BY csl.[statusId], s.[managerINN]

I need to use query below somehow, but I don't know how:

select top 1 * from sm.CandidatesStatusesLog
order by logStatusId  desc

Table CandidatesStatusesLog. Here for candidate 58 I only need to get value 7, not both 5 and 7.

Answer 1

A simple option uses row_number():

SELECT COUNT(*) as resumesCount, csl.[statusId], s.[managerINN] 
FROM sm.Candidates c
INNER JOIN sm.CandidateVacancies cv on c.[candidateId] = cv.[candidateId]
INNER JOIN sm.Staff s on s.[staffId] = cv.[vacancyId]
INNER JOIN (
    SELECT csl.*, ROW_NUMBER() OVER(PARTITION BY [candidateId] ORDER BY logStatusId DESC) as rn
    FROM sm.CandidatesStatusesLog csl
) csl on c.[candidateId] = csl.[candidateId]
WHERE cs1.rn = 1
GROUP BY csl.[statusId], s.[managerINN]