Sql 2005 get latest entry from a group of data

Question

Problem:

I have a set of data that keeps track of history of communication between clients. So each client has their own activity history (notes, emails..etc). I need to get the latest entry from each client's activity history, where each row is the latest entry from a client's activity. So every row is unique on client id and we can call each activity, "description".

Each activity has a date, and some other jargon like subject, body.

Activity Table

activityid  |  clientid  |  subject  |  body  |  datemodified

Thanks!

Answer 1

You're not giving us much to go off of but here is a shot in the dark:

SELECT ClientId, body, MAX(DateModified)
FROM Activity
GROUP BY ClientId, body

Answer 2

SELECT ClientId, Body, MAX(DateModified)
  FROM Activity
 GROUP
    BY ClientId, Body;

Answer 3

Assuming SQL Server 2005+

;with cte AS
(
SELECT activityid, clientid, subject, body, datemodified,
ROW_NUMBER() OVER (PARTITION BY clientid ORDER BY datemodified DESC) AS RN
FROM Activity
)
SELECT activityid, clientid, subject, body, datemodified
 FROM cte
 WHERE RN=1

Answer 4

Assuming that what You are asking for is the last entered line for each client You can use a sentence like this:

SELECT activityid, clientid, subject, body, datemodified
FROM Activity as ac
WHERE datemodified=(
    SELECT max(datemodified)
    FROM Activity
    WHERE cleintid=ac.clientid
)