MAX function in where clause mysql [duplicate]

We can't reference the result of an aggregate function (for example MAX() ) in a WHERE clause of the same SELECT.

The normative pattern for solving this type of problem is to use an inline view, something like this:

SELECT t.firstName     , t.Lastname     , t.id  FROM mytable t  JOIN ( SELECT MAX(mx.id) AS max_id           FROM mytable mx       ) m    ON m.max_id = t.id

This is just one way to get the specified result. There are several other approaches to get the same result, and some of those can be much less efficient than others. Other answers demonstrate this approach:

 WHERE t.id = (SELECT MAX(id) FROM ... )

Sometimes, the simplest approach is to use an ORDER BY with a LIMIT. (Note that this syntax is specific to MySQL)

SELECT t.firstName     , t.Lastname     , t.id  FROM mytable t ORDER BY t.id DESC LIMIT 1

Note that this will return only one row; so if there is more than one row with the same id value, then this won't return all of them. (The first query will return ALL the rows that have the same id value.)

This approach can be extended to get more than one row, you could get the five rows that have the highest id values by changing it to LIMIT 5.

Note that performance of this approach is particularly dependent on a suitable index being available (i.e. with id as the PRIMARY KEY or as the leading column in another index.) A suitable index will improve performance of queries using all of these approaches.

Use a subselect:

SELECT row  FROM table  WHERE id=(    SELECT max(id) FROM table)

Note: ID must be unique, else multiple rows are returned

Some Mysql versions disallow 'limit' inside of a sub select. My answer to you (and me in the future) would be to use groups

select firstName,Lastname,id where {whatever}group by idhaving max(id)

This allows you to return whatever you want in the select area, without having an aggregate field.