MAX function in where clause mysql

Question

How can I use max() function in where clause of a mysql query, I am trying:

 select firstName,Lastname,MAX(id) as max where id=max;

this is giving me an error:

Unknown column 'max' in 'where clause'

Any Help? Thanks in advance.

Solution 1

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.

Solution 2

Use a subselect:

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

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

Solution 3

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 id
having max(id)

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

Solution 4

SELECT firstName, Lastname, MAX(id) as max WHERE YOUR_CONDITIONS_HERE HAVING id=max(id) 

Solution 5

Do you want the first and last name of the row with the largest id?

If so (and you were missing a FROM clause):

SELECT firstname, lastname, id
FROM foo
ORDER BY id DESC
LIMIT 1;

View more solutions

Comments

[Bill Karwin]

-1 You can't reference columns aliases in the WHERE clause, nor can you reference the result of aggregate functions in the WHERE clause. This answer is totally wrong.

[spencer7593]

The first sentence in this answer is correct i.e. max is used as the alias for an expression in the select list. But following that observation, the answer doesn't make any sense. Firstly, MAX is not a reserved word Ref: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html. What is the point of using a different alias? It doesn't address the problem.

[DDuffy]

If only i had seen this a month or 6 ago. Simple, and effective. Love it!

[frp farhan]

what should we enter in where if everything is sorted using group by and having....

[jasonseminara]

Remember that the having clause filters the temporary result set