Alex Bariyev Alex Bariyev - 7 months ago 15
SQL Question

Need help writing a query (LEFT JOIN)

There are research Proposals. Decisions are made on Proposals. Decisions can only be of 4 types. I need to display the latest decision on all reviewed proposals, associated with a certain researcher. This is what I have so far:

SELECT p.ProposalID
,p.Title
,p.DATE
,p.Submitted
,p.ReviewCompleted
,d.DecisionID
,dt.Description AS Decision
FROM Proposal p
LEFT JOIN (
SELECT TOP 1 *
FROM Decision
ORDER BY DATE
) d
ON d.ProposalID = p.ProposalID
LEFT JOIN DecisionType dt
ON dt.DecisionTypeID = d.DecisionTypeID
WHERE p.ReviewCompleted = '1'
AND p.ProposalID IN (
SELECT ProposalID
FROM Proposal
WHERE ResearcherID = ?
)
ORDER BY d.DATE


I understand that the problem comes from the first left join, but I can't figure out gow to get onlt the latest decision on p.ProposalID. Thank you so much in advance.

Answer

It seems you use MSSQL (add it to tags if so). In this case rewrite this LEFT JOIN using ROW_NUMBER() function:

LEFT JOIN (
    SELECT TOP 1 *
    FROM Decision
    ORDER BY DATE
    ) d
    ON d.ProposalID = p.ProposalID

to

LEFT JOIN (
    SELECT Decision.*, 
    ROW_NUMBER() OVER(PARTITION BY ProposalID ORDER BY DATE DESC) AS Row_num
    FROM Decision
    ) d
    ON (d.ProposalID = p.ProposalID)
       AND (Row_num = 1)

Here you need to calculate row numbers for each group and order by date. Then select only first (Row_num = 1) for each group.