CODEWITHSUNDEEP
sqlt-sql
AndrewMo
AndrewMo

Reputation: 149

T-SQL Select first instance

I have a table that contains patient locations. I'm trying to find the first patient location that is not the emergency department. I tried using MIN but since the locations have numbers in them it pulls the MIN location but not necessarily the first location. There is a datetime field associated with the location, but I'm not certain how to link the min datetime to the first location. Any help would be appreciated. My query looks something like this:

SELECT PatientName,
       MRN,
       CSN,
       MIN (LOC) as FirstUnit,
       MIN (DateTime)as FirstUnitTime
FROM Patients
WHERE LOC <> 'ED'

Upvotes: 2

Views: 1714

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

I presume that you want the first unit for each patient. If so, then you can use row_number():

select PatientName, MRN, CSN, LOC as FirstUnit, DateTime as FirstUnitTime
from (select p.*,
             row_number() over (partition by PatientName, MRN, CSN
                                order by datetime asc) as seqnum
      from Patients p
      where loc <> 'ED'
     ) p
where seqnum = 1;

row_number() assigns a sequential number to a group of rows, where the group is specified by the partition by clause. The numbers are in order, as defined by the order by clause. So, the oldest (first) row in each group is assigned a value of 1. The outside query chooses this row.

Upvotes: 4

Related Questions