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:
,dt.Description AS Decision
FROM Proposal p
LEFT JOIN (
SELECT TOP 1 *
ORDER BY DATE
ON d.ProposalID = p.ProposalID
LEFT JOIN DecisionType dt
ON dt.DecisionTypeID = d.DecisionTypeID
WHERE p.ReviewCompleted = '1'
AND p.ProposalID IN (
WHERE ResearcherID = ?
ORDER BY d.DATE
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
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.