MySQL Forums
Forum List  »  InnoDB

Subqueries and Aggregate Functions
Posted by: Ernesto Ocampo Herrera
Date: July 29, 2008 08:16AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Subqueries and Aggregate Functions
3099
July 29, 2008 08:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.