SQL experts! I Need SQL Help!?

Question

I have a table that contains a client_id, task_id, comment_text, posted_timestamp. Within the client id there can be many task ids so I would could see records with the same client_id with different task ids. Also there could be multiple records with the same client id and task id but with different comments posted to the task at different times. What I am trying to do is select the last posted comment per client id, task id. How would I do this using the Max function?

Answer 1

Not really an SQL expert.. but try this:

SELECT * FROM table_name WHERE client_id=X ORDER BY posted_timestamp DESC LIMIT Y

X would be the client_id you want to see posts on

Y would be the total number of records

Results will be sorted by timestamp.

Not sure why you would need the MAX statement

Answer 2

Couple of different ways to do this:

If your DBRM supports the LIMIT clause

SELECT client_id, task_id, comment_text, posted_timestamp

FROM yourTable

ORDER BY posted_timestamp DESC LIMIT 1

Otherwise, use a correlated subquery

SELECT A.client_id, A.task_id, A.comment_text

FROM yourTable A

WHERE A.posted_timestamp =

(SELECT MAX(posted_timestamp FROM yourTable B

WHERE A.client_id = B.client_id

AND A.task_id = B.task_id)

Answer 3

I don't think max is the answer.  I think you a big loop for the client, an inner loop for each task.  For each client/task you would do something like SELECT TOP 1 comment FROM table ORDER BY timestamp DESC

Answer 4

If I am not mistaken, you can use the MAX function on datetime datatypes. And if so, then this should work :

SELECT client_id, task_id, MAX(posted_timestamp)

FROM yourTable

GROUP BY client_id, task_id;

I am pretty sure this would work. It may need a little tweaking, but hopefully it will give you the general idea, and you can work it out from there. Good luck : )

UPDATE : this link may be helpful - a person asked a similar question, and the answerer gave a different approach that may help you

http://forums.devshed.com/mysql-help-4/s...