Menu
Community /Pin to ProfileBookmark
@NogDogMay 29.2013 — #If I'm guessing correctly as to what the criteria are:
This assumes that last_post is some sort of DATE, DATETIME, or TIMESTAMP column.
You'll want to make sure you have indexes on the case_id and last_post columns for best performance.
nested query; query within a query
Data
id case_id date_notated
1 25 06/01/2010
2 25 06/21/2011
3 25 09/03/2012
4 25 05/01/2013
5 26 05/02/2013
6 26 05/04/2013
7 26 05/05/2013
8 26 05/07/2013
Desired Result
id case_id last_post
4 25 05/01/2013
8 26 05/07/2013
how do i write a query to get the result?
thanks!
Sign in
to post a comment1 Comments(s) ↴
0
@NogDogMay 29.2013 — #If I'm guessing correctly as to what the criteria are:<i>
</i>SELECT id, case_id, last_post
FROM table_name AS t1
WHERE last_post = (
SELECT MAX(last_post)
FROM table_name t2
WHERE t1.case_id = t2.case_id
)
ORDER BY case_id -- assuming you want this ordering, otherwise change this
This assumes that last_post is some sort of DATE, DATETIME, or TIMESTAMP column.
You'll want to make sure you have indexes on the case_id and last_post columns for best performance.