Alex Bariyev - 10 months ago 33

SQL Question

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.