Mysql query for finding 10 close marks of class

I want nearest 10 marks for particular student. For exa开发者_运维百科mple John has marks 73 then i have to find 10 student with marks greater than 73 in ascending order nearest to John. My Table structure is (id,name,marks).

---

Try this:

SELECT id, name, marks
FROM table1
WHERE marks > (SELECT marks FROM table1 WHERE name = 'John')
ORDER BY marks
LIMIT 10

---

Select s.id, s.name, s.marks
From students sj
Join students s On ( s.marks > sj.marks )
Where sj.name = 'John'
Order By s.marks
Limit 10

sj will be student John (assuming that you have only one John), and s contains all records with marks greater than John's.

The Limit limits the output to ten rows.