SQL query to aggregate data from three tables
You need to get the maximum address_id per user_id

SELECT user_id,MAX(address_id) address_id
FROM users_addresses GROUP BY user_id;


Make that query a subquery and join back to the other tables

SELECT u.first_name
 , u.last_name
 , u.username
 , u.password
 , a.user_id
 , a.street
 , a.email
 , c.name 
FROM
(SELECT user_id,MAX(address_id) address_id FROM users_addresses GROUP BY user_id) ua
INNER JOIN users u           ON ua.user_id    = ua.user_id
INNER JOIN users_addresses a ON ua.address_id = a.address_id
INNER JOIN countries c       ON a.country_id  = c.country_id
ORDER BY u.last_name,u.firstname
;


Give it a Try !!!