SQL query for most recent date and constrained by another column

Question

Hello,

I have a table (PAT_PROCEDURES) with three columns: patient_id, procedure_id, procedure_date, and token_id that stores records about patients and procedures they've undergone as well as the procedure's date; token ID is a special numeric identifier.

I also have another table (PAT_TOKENS) with three columns patient_id and token_id, it has a subset of those patients from the first table, but the token ID is 0. I'm trying to update the token ID to be that of the most recent procedure performed from the first table, where the procedures is one of a finite list of procedure IDS....basically here's what I have so far:

merge into pat_tokens t 
using (
  -- select all patients with most recent procedure among procedure IDs (45, 66, 78)
) procs on (t.patient_id = procs.patient_id)
when matched then
 update set t.token_id = procs.token_id

The commented select query is what i'm struggling with.

THanks!

Answer 1

 select patient_id 
 from PAT_procedure P
  inner join (select procedure_id, max(procedure_date) as last_date
              from PAT_procedure
              where procedure_id in (45, 66, 78)
              group by procedure_id) mx
  on p.procedure_id = mx.procedure_id and p.procedure_date = mx.last_date

Answer 2

You didn't say which DBMS, but assuming SQL 2008 for now since it has MERGE...

UPDATE t
SET t.token_id = x.token_id
FROM
   pat_tokens t
   CROSS APPLY (
      SELECT TOP 1 p.token_id
      FROM pat_procedures p
      WHERE
         p.procedure_id IN (45, 66, 78)
         AND p.patient_id = t.patient_id
      ORDER BY p.procedure_date DESC
    ) x
WHERE t.token_id <> procs.token_id

I sure hope you have an index on patient_id in pat_procedures. It would be great if it's clustered, or even better if it's a nonclustered index with procedure_id, token_id, and procedure_date included, assuming the nonclustered index is narrower than the clustered index. Don't go creating this last index if you don't need it though.

I just realized I might have misunderstood, perhaps this is what you meant:

UPDATE t
SET t.token_id = x.token_id
FROM
   pat_tokens t
   CROSS APPLY (
      SELECT TOP 1 p.token_id, p.procedure_id
      FROM pat_procedures p
      WHERE p.patient_id = t.patient_id
      ORDER BY p.procedure_date DESC
    ) x
WHERE
   t.token_id <> x.token_id
   AND x.procedure_id IN (45, 66, 78)

Here's another method, just answering your question for the SELECT statement you're looking for this time:

SELECT patient_id
FROM (
   SELECT TOP 1 * WITH TIES
   FROM pat_procedures p
   ORDER BY p.procedure_date DESC
) x
WHERE x.procedure_id IN (45, 66, 78)

I'm not sure I have correct what you're looking for, but reply back and I'll update as necessary.

UPDATE

Now that I know it's Oracle I won't be able to just whip out a query for you.

But I do know that Oracle supports windowing functions. If you can translate either of my queries above into the correct Oracle syntax that assigns number 1 to each row with the most recent date inside a derived table, then join to that where the procedure ids are correct, you should have it. I'll try to get back to this tomorrow...