sql - MAX function in where clause mysql -

how can use max() function in clause of mysql query, trying:

 select firstname,lastname,max(id) max id=max; 

this giving me error:

unknown column 'max' in 'where clause' 

any help? in advance.

you can't reference result of aggregate function (e.g. max() ) in clause of same query.

the normative pattern solving type of problem use inline view, this:

select t.firstname      , t.lastname      , t.id   mytable t   join ( select max(mx.id) max_id            mytable mx        ) m     on m.max_id = t.id 

this 1 way specified result. there several other approaches same result, , of can less efficient others. other answers demonstrate approach:

 t.id = (select max(id) ... ) 

sometimes, simplest approach use order limit. (note syntax specific mysql)

select t.firstname      , t.lastname      , t.id   mytable t  order t.id desc  limit 1 

note return 1 row; if there more 1 row same id value, won't return of them. (the first query return rows have same id value.)

this approach can extended more 1 row, 5 rows have highest id values changing limit 5.

note performance of approach particularly dependent on suitable index being available (i.e. id primary key or leading column in index.) suitable index improve performance of queries using of these approaches.