query structure question
Posted: Tue May 28, 2013 5:27 pm
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]
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]