SQL Subquery in the FROM clause

Question

I've found a few questions that deal with this problem, and it appears that MySQL doesn't allow it. That's fine, I don't have to have a subquery in the FROM clause. However, I don't know how to get around it. Here's my setup:

I have a metrics table that has 3 columns I want: ControllerID, TimeStamp, and State. Basically, a data gathering engine contacts each controller in the database every 5 minutes and sticks an entry in the metrics table. The table has those three columns, plus a MetricsID that I don't care about. Maybe there is a better way to store those metrics, but I don't know it. Regardless, I want a view that takes the most recent TimeStamp for each of the different ControllerIDs and grabs the TimeStamp, ControllerID, and State. So if there are 4 controllers, the view should always have 4 rows, each with a different controller, along with its most recent state.

I've been able to create a query that gets what I want, but it relies on a subquery in the FROM clause, something that isn't allowed in a view. Here is what I have so far:

SELECT *
FROM
    (SELECT 
    ControllerID, TimeStamp, State
    FROM Metrics
    ORDER BY TimeStamp DESC)
AS t
GROUP BY ControllerID;

Like I said, this works great. But I can't use it in a view. I've tried using the max() function, but as per here: SQL: Any straightforward way to order results FIRST, THEN group by another column? if I want any additional columns besides the GROUP BY and ORDER BY columns, max() doesn't work. I've confirmed this limitation, it doesn't work.

I've also tried to alter the metrics table to order by TimeStamp. That doesn't work either; the wrong rows are kept.

Edit: Here is the SHOW CREATE TABLE of the Metrics table I am pulling from:

 CREATE TABLE Metrics (
  MetricsID int(11) NOT NULL AUTO_INCREMENT,
  ControllerID int(11) NOT NULL,
  TimeStamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  State tinyint(4) NOT NULL,
  PRIMARY KEY (MetricsID),
  KEY makeItFast (ControllerID,MetricsID),
  KEY fast (ControllerID,TimeStamp),
  KEY fast2 (MetricsID),
  KEY MetricsID (MetricsID),
  KEY TimeStamp (TimeStamp)
) ENGINE=InnoDB AUTO_INCREMENT=8958 DEFAULT CHARSET=latin1

Answer 1

If you want the most recent row for each controller, the following is view friendly:

SELECT ControllerID, TimeStamp, State
FROM Metrics m
WHERE NOT EXISTS (SELECT 1
                  FROM Metrics m2
                  WHERE m2.ControllerId = m.ControllerId and m2.Timestamp > m.TimeStamp
                 );

Your query is not correct anyway, because it uses a MySQL extension that is not guaranteed to work. The value for state doesn't necessary come from the row with the largest timestamp. It comes from an arbitrary row.

EDIT:

For best performance, you want an index on Metrics(ControllerId, Timestamp).