SQL Server Developer >> Tricky subquery
Other alternative solutions for the subquery are:
1.- Using SELECT TOP 1 ... ORDER BY [Date] DESC
2.- "Denormalizing" the DB a little and add a LastStatusID column to your
Project table.
If the inserts on StatusHistory are chronological, then an INSERT
trigger on StatusHistory could do the job of updating Project.LastStatusID
I would definetely go for option 2 if you have many locks on StatusHistory.
Regards,
Eric Garza
AMIGE
> Hi all,
>
> How would you write a SELECT query for returning all Projects whose
current
> StatusID = 10, given the following schema:
>
> Project Table:
> ID - identity
> Name
>
> StatusHistory:
> ID - identity
> ProjectID - fkey
> StatusID
> Date
>
> I've been using MAX(Date) but perhaps there is a better approach?
>
> TIA,
> --
> Jon
SQL Server Developer >> Tricky subquery
SELECT P.id, P.name
FROM Projects AS P
WHERE EXISTS
(SELECT *
FROM StatusHistory
WHERE projectid = P.id
HAVING MAX(CASE WHEN statusid = 10 THEN date END)=MAX(date))
Why ProjectID in StatusHistory but ID in Projects? Convention is that each
data element has a unique and consistent name wherever it appears.