Select / Group by query
-
Hi All
This most likely has a simple solution (here's hoping
) but I'm stumped.I've got a table (tblTemp) with the following columns : id, dateModified, clientId, Comments.
I want to get only the latest comments for each client.
The initial code I've got that works (without retrieving the [Comments] column) is:
select Max(DateModified) as DateModified, clientId
from [tblTemp]
group by clientId
But as soon as I add [Comments] in, I naturally get all rows as comments are different for each entry.
So my question is : Is there a way to get:
1. all the columns
2. for only the latest entry
3. per clientId?
Hope I've explained that well. Thanks in advance for any suggestions.
Regards, Alison
-
SELECT T.id, T.dateModified, T.clientId, T.Comments
FROM [tblTemp] T
INNER JOIN (select Max(DateModified) as LatestDateModified, clientId
from [tblTemp]
group by clientId) DT ON T.clientId = DT.clientId AND T.dateModified = DT.LatestDateModified
_____________
Code for TallyGenerator -
Thanks Sergiy
That works really well. Appreciate the help and time.
Regards, Alison
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply