mysql> select * from tb2 a where not exists (select 'x' from tb2 where gid=a.gid and col2>a.col2); +----+------+------+------+ | id | gid | col1 | col2 | +----+------+------+------+ | 6 | A | 29 | 97 | | 15 | E | 14 | 86 | | 24 | D | 54 | 79 | | 28 | C | 34 | 90 | | 32 | B | 4 | 90 | +----+------+------+------+ 5 rows in set (0.00 sec)