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

User · Answer

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

User · Answer

You are using word  max  as an alias for your column  Try to   MAX id  as mymax     WHERE ID - mymax

User · Answer

Use a subselect   SELECT row  FROM table  WHERE id       SELECT max id  FROM table     Note  ID must be unique  else multiple rows are returned

User · Answer

This query should give you back the data you want  Replace foo with the table name you are using   SQL Query    select firstName Lastname  id  from foo  having max id    id

User · Answer

The syntax you have used is incorrect  The query should be something like   SELECT column name s  FROM tablename WHERE id    SELECT MAX id  FROM tablename

User · Answer

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

User · Answer

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

User · Answer

SELECT firstName  Lastname  MAX id  as max WHERE YOUR CONDITIONS HERE HAVING id max id

[mysql] MAX function in where clause mysql

Examples related to mysql

Examples related to sql

Examples related to max

Examples related to greatest-n-per-group