Aggregate Query

[Leighton21]

Hi all,

I have the following table (including data)

Date Product Weight
12/06/2008 4:15:00 AM A 100
12/06/2008 4:30:00 AM B 100
12/06/2008 5:00:00 AM B 100

I have grouped the data by the hour (i.e. all above is grouped into 1 record) so I get

Date Product Weight
12/06/2008 5:00:00 AM ? 300

Is there a simple way make the query select the most recent product when rolling up the data in this way?
So from the original data you can see that the last product was B I want to roll the data up by the hour but I dont want to group by product I would like the query to select the latest product. i.e. B at 12/06/2008 5:00:00 AM B

cheers

 

[PDreyer]

select * into #t1 from (select          /* testdata
Date                   ,Product ,Weight */
'12/06/2008 4:15:00 AM','A'     ,100 union all select
'12/06/2008 4:30:00 AM','B'     ,100 union all select
'12/06/2008 5:00:00 AM','B'     ,100 union all select
'12/06/2008 5:30:00 AM','B'     ,100 union all select
'12/06/2008 5:45:00 AM','B'     ,100   )testdata(
Date                   ,Product,Weight)

select v1.hh,t1.Product,v1.sumw from (
  select dateadd(hh,datediff(hh,'20000101',dateadd(ss,3599,date)),'20000101')
  , max(Date), sum(Weight)
  from #t1
  group by dateadd(hh,datediff(hh,'20000101',dateadd(ss,3599,date)),'20000101')
)v1(hh,maxt,sumw)
join #t1 t1
on v1.maxt=t1.Date

drop table #t1

 

[Leighton21]

Cheers Mate