Mysql - Geting max field?

Question

THis is Our table

John 2
Mary 2
John 3
John 5
Calin 3
i want get this list where max(position) is right
But when i want to have position for 2 John appears to the list.
Max John position is 5
How could I make it right:
select name,max(position) where position=2 group by name;

Answer 1

Here's a discussion that should help
http://lists.mysql.com/mysql/200959

///

Answer 2

select name,max(position) from table group by name

Answer 3

select * from
(
select name,max(position) as Position from T1 group by name
) T2 where position=2

--T1 is Your Original Table
--T2 is virtual

The inner side gives results
name === Position
---------------------------------
Calin === 3
John === 5
Mary === 2

make it as T2

from T2 it can query again to find where position=2

The result will be
name === Position
---------------------------------
Mary === 2