Mysql Max Function Mixing Rows

Seems like i'm having fundamental problems using MAX - it's mixing the contents of rows, i think. There is a View: id rev state 1 100 pass 1 99 fail 1 98 fail

Solution 1:

You need a GROUP BY clause with the aggregate MAX(). MySQL permits you to omit it (where other RDBMS would report errors) but with indeterminate results, which you are seeing. This can be handled by joining against a subquery which returns the grouped rev per id.

SELECT 
  r.id,
  r.state,
  maxrev.rev
FROM
  VIEW_data r
  /* INNER JOIN against subquery which returns MAX(rev) per id only */
  JOIN (
    SELECT id, MAX(rev) AS rev
    FROM VIEW_data GROUPBY id
  /* JOINison both id and rev to pull the correct value for state */
  ) maxrev  ON r.id = maxrev.id AND r.rev = maxrev.rev
WHERE r.id = 1

http://sqlfiddle.com/#!2/4f651/8

The above will return the max rev value for any id. If you are certain you only need the one row as filtered by the WHERE clause rather than the MAX() per group, look at the other answer which makes use of ORDER BY & LIMIT.

Solution 2:

Try

SELECT r.id, r.state, r.revision
FROM VIEW_data r
WHERE r.id =1ORDERBY r.revision DESC
LIMIT 0,1