query structure question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
inosent1
Forum Commoner
Posts: 97
Joined: Wed Jan 28, 2009 12:18 pm

query structure question

Post 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]
Last edited by pickle on Fri May 31, 2013 3:06 pm, edited 1 time in total.
Reason: Added syntax tags
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: query structure question

Post 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`?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: query structure question

Post by Christopher »

I think you might be able to do something like:

SELECT case_id, MAX(date_notated) FROM notes GROUP BY case_id
(#10850)
Post Reply