How to show 5 maximum value after joining two tables?

I have 2 tables:

users(uid, rollno, name)

users table

results(rid, uid, marks)

results table

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

​x
 
MCA-03/17   16MCA-01/17   15MCA-04/17   8MCA-02/18   6MCA-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

 
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

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

Advertisement

Answer