MAX Column Value for Distinct Value on Different column SQL Server 2008 R2

Question

I have data that comes in the following format:

VISIT ID | DATETIME                 | ORDER NUM
123      | 2012-01-01 11:21:00:0000 | 111
123      | 2012-01-01 12:15:00:0000 | 223
...

So it is entirely possible for the same visit id to have multiple orders for the same exact thing, for example, two different orders for discharge, one might get canceled, and replaced by another.

What I want for a result would be something like the following:

VISIT ID | DATE       | TIME     | ORDER NUM
123      | 2012-01-01 | 12:15:00 | 223
...

All order numbers will be distinct, so if someone gets multiple orders for an x-ray, all order numbers will be unique. I am trying to obtain the MAX() order number for a visit id, so that the results only output one row as shown above.

I have been looking at the following links for some guidance but unfortunately I am most definitely missing something and not connecting the dots.

Max Value on Distinct column value

an answer to above

one of my own former questions - this will not work because almost all Discharge orders get discontinued when the patient actually gets discharged.

another post

Here is the code that I have so far:

select episode_no
, CAST(ent_dtime as DATE) as [Date]
, CAST(ent_dtime as time) as [Time]
, ord_no = (select top 1 t1.ord_no from smsmir.sr_ord t1)

from smsmir.sr_ord
WHERE svc_desc = 'DISCHARGE TO'
AND episode_no < '200000000'
group by episode_no, ord_no, ent_dtime
order by episode_no

I have also tried the following:

select episode_no
, CAST(ent_dtime as DATE) as [Date]
, CAST(ent_dtime as time) as [Time]
, MAX(ord_no)

from smsmir.sr_ord
WHERE svc_desc = 'DISCHARGE TO'
AND episode_no < '200000000'
group by episode_no, ord_no, ent_dtime
order by episode_no

Any direction would be great, thank you.

EDIT

When I use the following query:

SELECT EPISODE_NO
, MAX(ORD_NO)

FROM SMSMIR.SR_ORD

WHERE SVC_DESC = 'DISCHARGE TO'
AND EPISODE_NO < '20000000'
GROUP BY EPISODE_NO

I get what I need, but when I add in the datetime field it all goes to plop.

@BogdanSahlean I would not even use an order status, reason being is that most discharge orders will get cancelled, but not all of them, so if I use that I'll miss orders. So I really need to figure out how to obtain the `MAX(ord_no)` for a given `visit_id` — MCP_infiltrator, Dec 24 '13 at 16:27

score 1 · Accepted Answer · answered Dec 24 '13 at 16:32

Try this query:

SELECT  *
FROM (
    SELECT EPISODE_NO
        , ORD_NO
        , [DATE TIME COLUMN]
        , ROW_NUMBER() OVER(PARTITION BY EPISODE_NO ORDER BY ORD_NO) AS RowNum
    FROM    SMSMIR.SR_ORD
    WHERE   SVC_DESC = 'DISCHARGE TO'
    AND     EPISODE_NO < '20000000'
) src
WHERE src.RowNum = 1

MAX Column Value for Distinct Value on Different column SQL Server 2008 R2

1 Answers1