> -----Original Message----- > From: Daevid Vincent > Sent: Friday, February 11, 2005 14:32 > To: mysql@lists.mysql.com > Subject: Need help with historic aggregation of data > > I need to get the aggregate data from various tables for a report. > > The idea is that we audit devices daily on a schedule, and > also allow users > to audit the devices by choosing certain tests to run. It is > also the case > that new tests are added daily. So the scheduled test today > has more tests > than yesterdays and that has more than the day before's, etc. > > I want to get a report that shows ALL tests ever run on the > device in it's > lifetime, but only the most recent of each test (and the date > it was from). > > So if I ran tests like this: > > Date Device Test Result > ----- ------ ----- ------ > > 02/01 1 100 [scheduled] blah blah blah... > 02/01 1 101 [scheduled] blah blah blah... > 02/01 1 102 [scheduled] blah blah blah... > 02/01 1 105 [one off] foo foo foo... > > 02/02 1 100 [scheduled] blah blah blah... > 02/02 1 101 [scheduled] blah blah blah... > 02/02 1 102 [scheduled] blah blah blah... > 02/02 1 103 [scheduled] ble ble ble... > 02/02 1 106 [one off] bar bar bar... > > 02/03 1 100 [scheduled] blah blah blah... > 02/03 1 101 [scheduled] blah blah blah... > 02/03 1 102 [scheduled] blah blah blah... > 02/03 1 103 [scheduled] ble ble ble... > 02/03 1 104 [scheduled] blo blo blo... > > 02/01 2 100 [scheduled] blah blah blah... > 02/01 2 101 [scheduled] blah blah blah... > 02/01 2 102 [scheduled] blah blah blah... > 02/01 2 106 [one off] bar bar bar... > ... Etc ... > > What I'd expect to get back for device 1 is > > Test Date > ---- ----- > 100 02/03 this is more current than others > 101 02/03 this is more current than others > 102 02/03 this is more current than others > 103 02/03 this is more current than others > 104 02/03 this is more current than others > 105 02/01 since this was run long ago once > 106 02/02 since this was ran recently >
SELECT device,test,MAX(date) FROM my_table GROUP BY device,test ORDER BY device,test This will give you all devices. and SELECT test,MAX(date) FROM my_table WHERE device=1 GROUP BY test ORDER BY test will give you results for device 1. > My actual tables are pretty huge, and I'll spare you them. I > also am coding > this in PHP, in case I need to split this task up somehow. We > are using > v4.0.18 and can't change. > > I'm hoping there is some magic incantation of MAX(), GROUP > BY, DISTINCT, > that will harvest this info for me. > > Thanks in advance, > > Daevid. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
