MSSQL : Selecting a value from a row that has a column with minimun creation time for given parameters

[Prasad A]

Hi,
I want to know if there is a simple and efficient way to select a value from a row that has a column with minimum creation time for the given parameters.
Let us take a sample table named Patient

PatientId Observation Time Value

1 Temp 2014-02-19 03:55:00 35
1 Temp 2014-02-19 03:45:00 37
1 Weight 2014-02-19 03:40:00 60

If i am given the PatientId and Observation, I need to retrieve the value field with minimum creation time
For patient id 1 and observation Temp this would be Value 37
since it has 2014-02-19 03:45:00 as minimum creation time

[GuyThiebaut]

This should work:

select top 1 Value
from Patient
where PatientId = 1
and Observation = 'Temp'
and Time in
(
select min(Time)
from Patient
where PatientId = 1
and Observation = 'Temp'
)

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[Prasad A]

Thanks. That seems to be simple. If i have numerous entries for the specific patiend id and observation, will the use of top be performance efficient ?

[GuyThiebaut]

Top ensures that only one line is returned. You can remove the top 1 - however if more than one line is returned you will have to make a choice at some point if the values are different. Simply replace the top 1 with distinct if you know there will only be one value returned.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens