How do I get 5 records before AND after a record with a specific ID?

Question

I have a table named scores with the columns id and score. I want to access a specific record by its id as well as the 5 records before and after it. Is there a way in SQL to say "grab the score with the id of n and x items before and after it?"

Answer 1

Try:

  SELECT * 
    FROM scores 
   WHERE score >= n
ORDER BY score ASC
   LIMIT 6

 UNION

  SELECT * 
    FROM scores 
   WHERE score < n
ORDER BY score DESC
   LIMIT 5

The syntax may vary somewhat depending upon what Database server you are using.