Page 1 of 1

query structure question

Posted: Tue May 28, 2013 5:27 pm
by inosent1
i am not sure how to phrase this

i want to pull all records from the DB where i end up with the most recent record posted for a particular case.

for example, the table to be queried is 'notes'

'notes' has 50,000 records, representing 1000 records. let's then say that is an average of 50 notes per case.

each case has a unique case_id number. each record has a unique id number

what i want to do is look at all 1000 unique cases, with their respective set of 50 records, find the data for the last record posted (to be exact, the specific contents of one specific field), and move to the next, until i have 1000 unique records returned with their most recent date_field posted showing.

Example
[syntax]
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
[/syntax]

The result would be:
[syntax]
case_id last_post
25 05/01/2013
26 05/07/2013[/syntax]

Re: query structure question

Posted: Fri May 31, 2013 3:07 pm
by pickle
This may not be the most efficient way to do it, but what if you select all ordered by `date_notated`, then group by `case_id`?

Re: query structure question

Posted: Sat Jun 01, 2013 5:21 pm
by Christopher
I think you might be able to do something like:

SELECT case_id, MAX(date_notated) FROM notes GROUP BY case_id