Subqueries and Aggregate Functions
In my application, I handle machines which can produce several products, one at a time. Each machine, at every moment, has one type of product assigned. I'm using MySQL 5.0.
I have a table for MACHINES, a table for PRODUCTS, and a table for the ASSIGNMENT of products to machines. The ASSIGNMENT table holds the history of all assignment of products to machines, it looks like this:
Id__|Machine_Id__|Product_Id__|Date_________|Time
1___|7__________|28_________|2008-07-03___|12:11:32
2___|7__________|28_________|2008-07-03___|12:11:35
3___|7__________|27_________|2008-07-03___|12:11:38
4___|7__________|23_________|2008-07-03___|12:11:40
5___|4__________|21_________|2008-07-03___|12:11:47
For example, assignment 1 says that product 28 was assigned to machine 7 on 2008-07-03 12:11:32.
Here I have the history of assignments, but sometimes I need to know which product is CURRENTLY assigned to each machine. For that purpose, I have written the following query:
SELECT Id, Machine_Id Machine, Product_Id, Date, Time
FROM assignment
WHERE
(Date + INTERVAL TIME_TO_SEC(Time) SECOND) =
(SELECT Max(Date + INTERVAL TIME_TO_SEC(Time) SECOND) FROM assignmentWHERE Machine_Id=Machine);
This actually works, because each row is tested to have the maximum Date+Time for its machine. However, it is too slow even when the table has no more than 800 records (takes 1,5 seconds). It will be no good when the table has 10000 records.
The problem is that the outer query is over the total of records. There is a simple query that returns the most recent Date+Time of assignment of each machine:
SELECT Machine_Id, Max(Date + INTERVAL TIME_TO_SECTime) SECOND)
FROM assignment
GROUP BY Machine_Id
This returs the Date+Time of assignemnt of each machine really fast, but I also need to know which product was assigned! What I need to know is FROM WHICH ROW each value of Max(Date + INTERVAL TIME_TO_SECTime) SECOND) was fetched, and get the Product_Id column value of that row. I have not found any MySql function that gives me that, but may be someone knows one.
Any suggestions, please?
I should say that a query like
SELECT Machine_Id, Product_Id, Max(Date + INTERVAL TIME_TO_SECTime) SECOND)
FROM assignment
GROUP BY Machine_Id
does not work because (as stated in MySQL 5.0 GROUP BY reference), if Product_Id is in the SELECT clause but not in the GROUP BY clause, and not aggregate function is used like MAX or SUM, then its value will be any (undefined) one from the set of rows with a given Machine_Id.