CODEWITHSUNDEEP
sql-servermax
KAE
KAE

Reputation: 855

Find time of a MAX value in SQL Server

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?

Upvotes: 3

Views: 3271

Answers (1)

Martin Smith
Martin Smith

Reputation: 452978

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

Upvotes: 4

Related Questions