1

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

Result should be:

id   rev   state
1    100   **pass**

but i get this on the query below

id   rev   state
1    100   **fail**

SELECT r.id, r.state, MAX(r.revision)
FROM VIEW_data r
WHERE r.id=1
glutz
  • 1,889
  • 7
  • 29
  • 44

2 Answers2

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 GROUP BY id
  /* JOIN is on 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.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • this does work amazingly. i still do not understand why the query i had would mix the contents of rows. is it just a mysql thing? – glutz Jun 08 '13 at 04:32
  • 1
    @glutz It is a MySQL thing, maybe SQLite too. If you tried your original query on SQL Server for example, you would have gotten errs like _r.id is invalid in the SELECT list because it is not contained in GROUP BY_. Columns in the `SELECT` should be in the `GROUP BY`- MySQL's default behavior doesn't require it, and instead choose an arbitrary value to fill in the group because it can't choose a row unambiguously. People who learn aggregate functions on MySQL often have to re-learn them the "right way" when beginning to work with Oracle, MSSQL, Postgres, etc... – Michael Berkowski Jun 08 '13 at 11:56
1

Try

SELECT r.id, r.state, r.revision
FROM VIEW_data r
WHERE r.id = 1
ORDER BY r.revision DESC
LIMIT 0,1
invisal
  • 11,075
  • 4
  • 33
  • 54