sql show partition based on calculated column with mysql -

let's pretend have relation:

╔═══════════════════╗ ║ i++ name  score   ║ ╠═══════════════════╣ ║ 1    123     ║ ║ 2   joe   100     ║ ║ 3   bill  99      ║ ║ 4   max   89      ║ ║ 5   jan   43      ║ ║ 6   susi  42      ║ ║ 7   chris 11      ║ ║ 8   noa   9       ║ ║ 9   sisi  4       ║ ╚═══════════════════╝ 

now need subset based on data searching for. instance i'm searching fith place. in result need more record of jan, need 2 records before jan , 2 records behind jan too. have following resultset:

╔═══════════════════╗ ║ id++ name score   ║ ╠═══════════════════╣ ║ 3   bill  99      ║ ║ 4   max   89      ║ ║ 5   jan   43      ║ ║ 6   susi  42      ║ ║ 7   chris 11      ║ ╚═══════════════════╝ 

that sql got:

select @a:= id quiz.score username = 'jan';  set @i=0; select @i:=@i+1 platz, s.*  quiz.score s id between @a-5 , @a+5  order points desc; 

the problem here @a id of record. there way use calculated value @i:=@i+1?

thx lot help.

if not need rank in output (and appears comments , favored answers not), can combine quiz scores nearest jan's score:

query (sql fiddle here):

-- xxx assumes `scores`.`username` unique ! select * (     -- scored worse (or tied)     (    select s.*            scores s      cross join (select score scores username = 'jan') ref           s.score <= ref.score , username <> 'jan'        order s.score desc           limit 2)     union     -- our reference point record     (select s.* scores s username = 'jan')     union     -- scored better     (    select s.*            scores s      cross join (select score scores username = 'jan') ref           s.score > ref.score , username <> 'jan'        order s.score asc           limit 2) ) slice order score asc; 

(note limited results 2 records before jan , 2 after jan because sample data set small.)

those parentheses on constituent queries above needed allow limit , union work together. outermost query lets order results of union.