Detecting last records entered in a table

DW_Scott · #1 (**permalink**) 08-13-10, 09:44

Hello,

I need to select the last record that was entered for a each case_id in a table and then see if that each last record for each case_id was made in the last 7 days.

This returned query is to populate an alarm that the case has not had enough contact.

The following code almost works - however it selects the first record in the table for the case and not the last. ie if case_id 3 has four records: 2010-07-01, 2010-08-02, 2010-08-10, 2010-08-11 and todays date is 2010-08-13, it always seems to stop at 2010-07-01 and return that the case has not had enough contact.

I have tried a number of things but cant quite get it to work.

Does anyone have a suggestion.

SELECT case_notes.case_id As CaseID, case_management.contact_time As ContactTime, person.firstname, person.surname FROM case_notes,case_management, person WHERE case_notes.case_id = case_management.case_id AND ( date_sub(curdate(), interval 0 month) - case_notes.whenadded > 7 ) AND case_management.contact_time = 'Weekly' AND case_management.active = 'Active' AND case_management.person_id = person.person_id GROUP BY case_notes.case_id ORDER BY case_notes.case_id

thanks
Scott

r937 · #2 (**permalink**) 08-13-10, 10:24

Quote:

Originally Posted by DW_Scott

I need to select the last record that was entered for a each case_id

Code:

SELECT case_notes.case_id As CaseID
     , case_management.contact_time As ContactTime
     , person.firstname
     , person.surname 
  FROM ( SELECT case_id
              , MAX(whenadded) AS last_added
           FROM case_notes
         GROUP
             BY case_id ) AS m
INNER
  JOIN case_notes
    ON case_notes.case_id = m.case_id
   AND case_notes.whenadded = m.last_added
INNER
  JOIN case_management
    ON case_management.case_id = case_notes.case_id
   AND case_management.contact_time = 'Weekly' 
   AND case_management.active = 'Active' 
INNER
  JOIN person 
    ON person.person_id = case_management.person_id
 WHERE case_notes.whenadded >= CURRENT_DATE - INTERVAL 7 DAY
ORDER 
    BY case_notes.case_id

DW_Scott · #3 (**permalink**) 08-13-10, 19:25

Thanks for the reply.

I see what you are doing but am getting an error

Unknown column 'm.case_notes' in 'on clause'

The following works as I need - thanks- the results appear not to be available in the next join.

( SELECT case_id
, MAX(whenadded) AS last_added
FROM case_notes
GROUP
BY case_id ) As m

I have tried it in phpmyadmin and navicat

Any further ideas

r937 · #4 (**permalink**) 08-14-10, 00:09

Quote:

Originally Posted by DW_Scott

Unknown column 'm.case_notes' in 'on clause'

aaargh... typo

i fixed it, see above

DW_Scott · #5 (**permalink**) 08-14-10, 01:14

Thanks that works great - except for one thing.

If there are two entries for the case_note on the last day entered, the person is listed twice in the result.

Is there a way to limit the result to only one per case_id.

Thanks again