CODEWITHSUNDEEP
sqlsql-server
Krazy Klauz
Krazy Klauz

Reputation: 29

Subquery to pull single record by Max DateTime Field

I have a subquery (MS SQL 2016) to pull the most recent measurement value for a specific measure. I need to link the resulted measure value back to my main query by PAT_ID. The problem I'm running into is that I can't get the MAX Date (most recent date) to pull only one record in the subquery.

The following query returns numerous records and not the most recent record only

    SELECT distinct meas.MEAS_VALUE, rec.pat_id, MAX(meas.ENTRY_TIME) "MAX ET"
    from ip_flwsht_rec rec
        inner join [CLARITY].[dbo].[IP_FLWSHT_MEAS] meas on rec.fsd_id=meas.FSD_ID 
            and meas.flo_meas_id='14' 
            and meas.MEAS_VALUE is not null
    where meas.ENTRY_TIME>=(DATEADD(day, DATEDIFF(day, 0,getdate()) - 548, 0))
        AND rec.pat_id = 'CENSORED'
    GROUP BY meas.MEAS_VALUE, rec.PAT_ID

This returns the results...

PAT_ID is censored to protect the innocent

This same patient has 9 results but I only want the most recent. Not sure what I'm doing wrong and any assistance is greatly appreciated.

Upvotes: 0

Views: 225

Answers (3)

Dan Bracuk
Dan Bracuk

Reputation: 20794

Depending on your requirements, the top 1 approach in @Zack's answer might be the best approach. It features one of the most redeeming of all qualities, simplicity.

If the top 1 approach does not satisfy your requirements, then you are pretty close. In your question, you say, I need to link the resulted measure value back to my main query by PAT_ID. Actually, you need to link it with two fields, the pat_id, and the entry_time.

To ensure you only get one record per patient, change this:

SELECT distinct meas.MEAS_VALUE, rec.pat_id, MAX(meas.ENTRY_TIME) "MAX ET"

to this:

SELECT rec.pat_id, MAX(meas.ENTRY_TIME) "MAX ET"

Your final query then resembles this:

with mostRecentRecords as (
select rec.pat_id, MAX(meas.ENTRY_TIME) mostRecentEntry
from etc
group by rec.pat_id
)
select whatever
from your tables
join mostRecentRecords on rec.pat_id = mostRecentRecords.pat_id
     and meas.entry_time = mostRecentEntry

Upvotes: 2

z m
z m

Reputation: 1503

Just add TOP(1) in your select, and sort it by time:

SELECT distinct TOP(1) meas.MEAS_VALUE, ...
...
ORDER BY meas.ENTRY_TIME DESC

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

Using row_number() to order the meas records prior to joining, and partitioned by fsd_id. Resulting in the latest record from meas for each fds_id without the need for group by or aggregation.

select
    meas.MEAS_VALUE
  , rec.pat_id
  , [Max ET] = meas.ENTRY_TIME
from ip_flwsht_rec rec
  inner join (
    select *
      , rn = row_number() over (partition by fsd_id order by ENTRY_TIME desc)
    from [CLARITY].[dbo].[IP_FLWSHT_MEAS]
    ) as meas 
        on rec.fsd_id=meas.FSD_ID 
       and meas.rn = 1 /* just the latest row based on `entry_time` */
       and meas.flo_meas_id='14' 
       and meas.MEAS_VALUE is not null
where meas.ENTRY_TIME>=(dateadd(day, datediff(day, 0,getdate()) - 548, 0))
    and rec.pat_id = 'CENSORED'

Upvotes: 0

Related Questions