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
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
