Table Query

Question

Table Query

James Mace

Ten Centuries

Points: 1022
More actions
December 22, 2006 at 2:53 pm

#92059

I have the table that contains weather data for seven days in one-hour iterations. Essentially, I have 168 rows (24 hours*7 days). Anyway, my table structure looks like this: WeatherID, Year, Month, Day, Hour, Temp etc.. My question is how would I go about structuring a statement that would return the Hour that has the lowest temperature for each day?
Thanks in advance,
Jim

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

DaveItz SSCrazy Points: 2146 More actions · Answer 1

Select w.*
From WeatherReport w
Inner Join (
   Select Year, Month, Day, Temp=Min(Temp)
   From WeatherReport 
   Group By Year, Month, Day
) t on (w.Year=t.Year)
   and (w.Month=t.Month)
   and (w.Day=t.Day)
   and (w.Temp=t.Temp)

(This should show both hours, in case of a tie)

James Mace Ten Centuries Points: 1022 More actions · Answer 2

Thanks, Dave, but when I run that query the returns back nothing.

DaveItz SSCrazy Points: 2146 More actions · Answer 3

Strange.

It seems to work on my test sample:

declare @WeatherReport table(WeatherID int, Year smallint, Month tinyint, Day tinyint, Hour tinyint, Temp float)
insert into @WeatherReport
select 1, 2006, 12, 21, 1, 85 union all
select 2, 2006, 12, 21, 2, 83 union all
select 3, 2006, 12, 21, 3, 84 union all
select 4, 2006, 12, 22, 1, 86 union all
select 5, 2006, 12, 22, 2, 85.9 union all
select 6, 2006, 12, 22, 3, 84

James Mace Ten Centuries Points: 1022 More actions · Answer 4

That is strange, because I just ran it again against your temporary table, and it seems to work. I wonder why it will not work against my table

James Mace Ten Centuries Points: 1022 More actions · Answer 5

I think I figured it out, somewhat anyhow. I just forgot to take into account hours within a day that have the same temperature. I can manipulate that programmatically, however. I really only need the earliest hour.