abhis abhis - 5 months ago 7
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

Answer

You can use:

SELECT 
  `name`,
  `score`
FROM 
  `score`
WHERE 
  `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)