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]
query structure question
Moderator: General Moderators
query structure question
Last edited by pickle on Fri May 31, 2013 3:06 pm, edited 1 time in total.
Reason: Added syntax tags
Reason: Added syntax tags
Re: query structure question
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`?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: query structure question
I think you might be able to do something like:
SELECT case_id, MAX(date_notated) FROM notes GROUP BY case_id
SELECT case_id, MAX(date_notated) FROM notes GROUP BY case_id
(#10850)