Find time of a MAX value in SQL Server

Question

I have a table, Table1, containing the fields TimeStamp and Humidity, which have the values:

TimeStamp
'2010-09-29 11:05:29.6'
'2010-09-29 11:05:29.7'
'2010-09-29 11:05:29.8'
'2010-09-29 11:05:29.9'
'2010-09-29 11:05:30.0'

Humidity
15.291
17.379
16.857
16.335
15.813

I would like to run a query that returns the value of TimeStamp at the instant that Humidity is at its maximum. In this example it would return '2010-09-29 11:05:29.7' because that is when Humidity is its highest value, 17.379. I also want to limit the time range, so it would be something like

SELECT _TimeStamp from Table1 
WHERE Humidity = MAX(Humidity) AND 
_TimeStamp >= '2010-09-29 11:05:29.6' AND 
_TimeStamp <= '2010-09-29 11:05:30.0'

but this gives an error that aggregates are not permitted in a where clause. How should this query be written correctly?

Answer 1

SELECT TOP 1 _TimeStamp
 from Table1 
WHERE 
_TimeStamp BETWEEN '2010-09-29 11:05:29.6' AND  '2010-09-29 11:05:30.0'
ORDER BY Humidity DESC

Or SELECT TOP 1 WITH TIES _TimeStamp if you want to bring back all timestamps matching the max humidity.

For more complicated grouping scenarios you should investigate the ranking functions such as row_number

Answer 2

Im just curious. Wouldnt a more general way of solving this be like

SELECT _TimeStamp
FROM Table1
WHERE
HUMIDITY = (SELECT MAX(HUMIDITY) FROM Table1)
AND _TimeStamp BETWEEN '2010-09-29 11:05:29.6' AND  '2010-09-29 11:05:30.0'

?

Couldnt there be cases, where your "TOP n - ORDER BY" trick doesnt work? Please correct me if I`m wrong.