abhis abhis - 4 months ago 6
MySQL Question

get all second highest values from a mysql table

I have a table with two fields as follows,

name score
xyz 300
pqr 200
abc 300
mno 100
erp 200
yut 200

How can I retrieve all second highest score from above table using MySQL query. Tried as follows,

SELECT name, MAX( `score` )
FROM score
WHERE score < (
SELECT MAX( score )
FROM score )

but it returns single value.

Expected result ,

name score
pqr 200
erp 200
yut 200

Any help please


You can use:

  `score`=(SELECT DISTINCT `score` FROM `score` ORDER BY `score` DESC LIMIT 1,1)

Tip: naming table and it's column with same names will cause troubles in complicated queries (for you, not for DBMS)