Simple SQL problem

Question

I can't believe I'm getting so stuck on what seems like such a simple query.

I need to get back the User of a Log for a given Project which has the maximum DateLogged value. I've rewritten it a million ways but this is the way which expresses what I want the clearest -

SELECT L.User
FROM Log AS L
WHERE L.Id = 24
GROUP BY L.ProjectId
HAVING MAX(L.DateLogged) = L.DateLogged

But that throws a "Column 'Log.DateLogged' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause." But I don't want it grouped by DateLogged, nor do I want it in an aggregate function.

Answer 1

I think that if you use L.Id = 24, you are going to get only ONE row back. IMO you should be filtering by the L.User or L.UserId, not the L.Id

For a list of all projects, this is what you can do.

SELECT L.User, L.ProjectId, MAX (L.DateLogged) as DateLogged
FROM Log AS L
WHERE L.User = @YourUserNameGoesHere
GROUP BY L.User, L.ProjectId

For a single project that you know the project detail for, just use an ORDER BY

SELECT TOP 1 L.User, L.DateLogged
FROM Log AS L
WHERE L.User = @YourUserNameGoesHere
AND L.ProjectId = @YourProjectIdGoesHere
ORDER BY L.DateLogged DESC

Answer 2

I'm not sure if this is what you wanted, but perhaps these examples will help you.

Get user of a log @LogId on a project @ProjectId with max DateLogged:

SELECT TOP(1) L.User
FROM Log AS L
WHERE L.Id = @LogId AND L.ProjectId = @ProjectId
ORDER BY L.DateLogged DESC

Get users with max DateLogged in a log @LogId for every project:

SELECT L.User, L.ProjectId
FROM Log AS L
WHERE L.Id = @LogId
  AND L.DateLogged = (
    SELECT MAX(L2.DateLogged) FROM Log AS L2
    WHERE L2.Id = L.Id AND L2.ProjectId = L.ProjectId
  )

Answer 3

This is similar to YRH's answer, but uses a window function (ROWNUMBER) instead of a correlated sub-query - this can be more efficient:

DECLARE @ProjectId int
SELECT  L.[User],
        L.ProjectId
FROM    (SELECT L.[User],
                L.ProjectId
                ROW_NUMBER() OVER (PARTITION BY L.ProjectId ORDER BY DateLogged DESC) RN
         FROM   dbo.[Log] L
         WHERE L.Id = @LogId) L
WHERE   RN = 1