selecting multiple max values

Question

i have a table like this on a mysql database:

id | item
-----------
1  | 2
2  | 2
3  | 4
4  | 5
5  | 8
6  | 8
7  | 8

i want the result to be 3 record with the highest Item value

select max(item) returns only 1 value how can i select multiple max values? thank you

Answer 1

You can use a derived table to get the maximum value and join it back to the original table to see all rows corresponding to it.

select t.id, t.item 
from tablename t
join (select max(item) as mxitem from tablename) x
on x.mxitem = t.item

Edit:

select t.co_travelers_id, t.booking_id, t.accounts_id 
from a_co_travelers t
join (select accounts_id, max(booking_id) as mxitem 
      from a_co_travelers
      group by accounts_id) x 
on x.mxitem = t.booking_id and t.accounts_id = x.accounts_id