mysql inner join max

Question

I need your help with a inner join and max. I already researched other questions but I could not solve...

I have three tables:

• member (member_id, name)
• location (location_id, name, order)
• member_location (id, member_id, location_id)

I need select just the record from member_location with the highest order group by member_location.memberId.

Example:

Member
1, Jack Sparrow

Location
1, Mexico, 2
2, Punta Cana, 3
3, Cuba, 1

member_location
1, 1, 3
1, 1, 2
1, 1, 1

On member_location I have 3 records for the same member, with my query I need get the second row of member_location (1, 1, 2) because the order of location 2 is the greatest .

I try:

select ml.memberId, ml.locationId, max(l.order)
from member_location ml inner join
     location l
     on ml.locationId=l.id
group by ml.memberId;

result: 1,1,3 -The order it's ok but the locationId no.

I also try:

select ml.locationId, ml.memberId
from member_location ml inner join
     (select id, max(order) from location) l
     on ml.locationId = l.id
group by ml.memberId;

On the response I receive the record with the first location.

Answer 1

If you only want one row back, you can use order by and limit:

select ml.memberId, ml.locationId
from member_location ml inner join
     location l
     on ml.locationId=l.id
order by l.order desc
limit 1;