How to show 5 maximum value after joining two tables?

I have 2 tables:

• users(uid, rollno, name)

• results(rid, uid, marks)

So I want to show the highest 5 marks holder with their roll no and marks like-

MCA-03/17   16
MCA-01/17   15
MCA-04/17   8
MCA-02/18   6
MCA-06/17   5

I have tried:

SELECT u.rollno, r.marks 
FROM users u 
JOIN results r ON r.uid = u.uid 
ORDER BY marks DESC 
LIMIT 5

but it is not showing the top 5 highest marks.

Answer

You need to get the top 5 marks from results and join to users:

SELECT u.rollno, r.marks
FROM users u INNER JOIN (
  SELECT uid, marks 
  FROM results  
  ORDER BY marks DESC 
  LIMIT 5
) r ON r.uid = u.uid
ORDER BY marks DESC