SET @club = 2;
SET @group = 0;
SET @rank = 0;

SELECT
  *
FROM
  (SELECT
    ms.id
    , member_id
    , lastname
    , firstname
    , @rank := IF(@group = member_id, @rank + 1, 1) AS rank
    , @group := member_id AS grp
    , club_id
    , date_expiry
  FROM
    memberships ms
    , members m
    , clubs c
  WHERE club_id = @club
    AND ms.member_id = m.id
    AND ms.club_id = c.id
  ORDER BY member_id
    , date_expiry DESC) AS memberships
WHERE rank <= 1
ORDER BY lastname
  , firstname;