I am a bit confussed trying to write a subquery.

I have two tables Project and ProjectAllocationLog

project looks like this
projectID ProjectName
7578 ProjectOne
7579 ProjectTwo
7980 ProjectThree

ProjectAllocationLog looks like this
AllocationLogID projectID UserID
45 7578 10
46 7578 11
49 7579 12
50 7579 13
51 7579 14
53 7578 15
54 7980 16
55 7980 17

I am trying to find the maximum projectAllocationLogID per Project get a grid result as follows

ProjectID AllocationLogID UserID
7578 53 15
7579 51 14
7580 55 17

I have written this

SELECT projectid, userID, AllocationLogID
FROM projects p
WHERE projectid =
(SELECT max(projectAllocationlogid) FROM ProjectAllocationLog as AllocationLogID WHERE P.projectid = ProjectAllocationLog.Projectid);


but I have gone wrong somewhere and I cant work it out

any help appreciated

oo, I have managed to do it, here is the query should anyone wonder

SELECT max(projectAllocationlogid) as AllocationLogid, projectid, NewUserID
FROM projectAllocationLog
WHERE projectid IN (SELECT (projectid) FROM projects)
group by projectid, projectAllocationlogid, NewUserID


:)

Actually, i was wrong, this still doesn't work as I get multiple rows per Projectid

I think you were on the right track in both your attempts.

Here's a correlated subquery way:

SELECT projectid, userID, AllocationLogID
FROM projectAllocationLog P1
WHERE AllocationLogID = (SELECT MAX(AllocationLogID ) FROM projectAllocationLog P2 WHERE P2.ProjectID = P1.ProjectID)

Note the table in the FROM clause is the projectAllocationLog and not the Projects table as in your first attempt. If you were to need the projectName or other columns from the Projects table, just JOIN the projectAllocationLog to it on the projectID.

Another way using the IN clause:

SELECT projectid, userID, AllocationLogID
FROM projectAllocationLog
WHERE AllocationLogID in (SELECT MAX(AllocationLogID ) FROM projectAllocationLog GROUP BY projectID)

It's not clear to me which way is more efficient. Looking at the generated query plan for both I was a bit surprised to find the first query looked like it was a bit better. I'm not sure that would hold up if there were a large number of rows in the table, though. YMMV.

As an aside, I think you would be better served having a column which specifically defined what the "maximum" (by which I assume you really mean the "latest") entry for a given project is. I'm guessing your AllocationLogID is an identity column and you are using the fact that these values are increasing to be a surrogate for the time ordering of log entries. It works in this particular case, so there is no arguing with success, but having something like a EntryDate datetime value makes it clear that you are in fact searching for the latest entry rather than the largest ID, which may not be the same thing in every problem domain...

Jeff Mason
[email protected]

I was going to suggest:

Not sure that it's any better than Jeff's suggestions though.

--

Joe (Microsoft MVP - XML)

Many Thanks Jeff and Joe, your help was invaluble to me xx