First event per patients

Question

The attached code is supposed to return the first ORDER_PROC.ORDER_INST for each patient. I'm getting multiple records in some cases. Any suggestions on a better approach? Thanks Steve

SELECT DISTINCT  
  ORDER_PROC.PAT_ENC_CSN_ID as ordercsn, Min(ORDER_PROC.ORDER_INST) as 
  CodeStatus_Datetime, CLARITY_SER.PROV_NAME as CodeStatus_OrderProvider
FROM   
  ORDER_PROC  with(nolock) , ORDER_METRICS with(nolock) , CLARITY_SER 
  with(nolock) 

WHERE
  ORDER_PROC.ORDER_PROC_ID = ORDER_METRICS.ORDER_ID AND
  ORDER_METRICS.ORDERING_PROV_ID = CLARITY_SER.PROV_ID AND
  --ORDER_PROC.REASON_FOR_CANC_C IS  NULL AND
  (ORDER_PROC.PROC_CODE = 'COD1' OR 
  ORDER_PROC.PROC_CODE = 'COD2' OR 
  ORDER_PROC.PROC_CODE = 'COD3' 
  )
GROUP by 
ORDER_PROC.PAT_ENC_CSN_ID, ORDER_PROC.ORDER_INST,CLARITY_SER.PROV_NAME

Add some sample table data and the expected result - as formatted text, not images. (BTW, _that_ Steve Jones?) — jarlh, Nov 16 '17 at 15:52
Promote the use of explict `JOIN` sintaxis, Aaron Bertrand wrote a nice article [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) about it. — Juan Carlos Oropeza, Nov 16 '17 at 15:58
This looks like an ordering system. I would urge you not to splatter that NOLOCK hint all over the place unless you are ok with inconsistent and inaccurate results. https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/. You also should consider using ANSI-92 style joins, they have been around for more than 25 years now. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins — Sean Lange, Nov 16 '17 at 15:58
Possible duplicate of [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — underscore_d, Nov 16 '17 at 16:01

score 1 · Answer 1 · answered Nov 16 '17 at 15:58

Use ROW_NUMBER() to create partition for each patient

SELECT *
FROM (
    SELECT OP.PAT_ENC_CSN_ID as ordercsn, 
           OP.ORDER_INST,
           CodeStatus_Datetime, 
           CS.PROV_NAME as CodeStatus_OrderProvider,
           ROW_NUMBER() OVER (PARTITION BY OP.PAT_ENC_CSN_ID
                               ORDER BY OP.ORDER_INST) as rn
    FROM  ORDER_PROC  OP
    JOIN  ORDER_METRICS OM
      OP.ORDER_PROC_ID = OM.ORDER_ID 
    JOIN CLARITY_SER  CS
      OM.ORDERING_PROV_ID = CS.PROV_ID 

    WHERE
      OP.PROC_CODE IN ('COD1','COD2','COD3')
   ) as T
 WHERE rn = 1

First event per patients

1 Answers1