mysql - Fetch two table data in single query and data should include only that row which has max rank -

i have 2 table users(id,name,email) and comments(id,user_id,rank,content). in comments table have user_id foreign key user table. want join users table comments table , want user table data along comment data(id,rank,content) highest rank only. tried mysql max function data return not correct. can guide how can achieve this.

input :

table 1: users attributes id, name, email

table 2: comments attributes id, user_id, rank, content

output : user table data comment table alongside having highest rank.

user_id | name | email | comment_id | rank | comment |

you can using join derived table has highest ranks.

select u.id, u.name, u.email, c.id, c.rank, c.content users u inner join comments c on u.id = c.user_id inner join  (select user_id, max(rank) highest_rank comments group user_id) x on x.user_id = c.user_id , x.highest_rank = c.rank 

here i'm using derived table x record highest comment rank each user, , using limit comment returned.

this postgresql