sql query for fetching 10 rows with near values

Question

Please help me with writing a sql query - I have a table with id, name and marks columns.

If I arrange the table in ascending order of marks, how can I fetch 5 names whose marks are close to a particular name.

Answer 1

Something like this should do it:

select id, name, marks
from Marks
where name <> 'User1'
order by abs(marks - (select marks from Marks where name = 'User1')) 
limit 5

Answer 2

Microsoft SQL Server

SELECT TOP 10 column FROM table

PostgreSQL and MySQL

SELECT column FROM table 
LIMIT 10

Oracle

SELECT column FROM table
WHERE ROWNUM >= 3 AND ROWNUM < 10

DB2

SELECT column FROM table
FETCH FIRST 10 ROWS

Answer 3

Something like this:

select * 
from marks m 
order by abs(m.mark - (select m2.mark from marks m2 where m2.name = "John Doe"))
limit 5;