sql - How to get this specific user rankings query in mysql? -

i've got tbl_items in user database want sort user rankings on particular item id (514). have test data on dev environment set of data:

mysql> select * tbl_items classid=514; +---------+---------+----------+ | ownerid | classid | quantity | +---------+---------+----------+ |       1 |     514 |        3 | |       2 |     514 |        5 | |       3 |     514 |       11 | |       4 |     514 |       46 | |       5 |     514 |       57 | |       6 |     514 |        6 | |       7 |     514 |        3 | |       8 |     514 |       27 | |      10 |     514 |        2 | |      11 |     514 |       73 | |      12 |     514 |       18 | |      13 |     514 |       31 | +---------+---------+----------+ 12 rows in set (0.00 sec) 

so far :) wrote following query:

set @row=0;  select a.*, @row:=@row+1 rank  (select a.ownerid,a.quantity tbl_items  a.classid=514) order quantity desc;  +---------+----------+------+ | ownerid | quantity | rank | +---------+----------+------+ |      11 |       73 |    1 | |       5 |       57 |    2 | |       4 |       46 |    3 | |      13 |       31 |    4 | |       8 |       27 |    5 | |      12 |       18 |    6 | |       3 |       11 |    7 | |       6 |        6 |    8 | |       2 |        5 |    9 | |       7 |        3 |   10 | |       1 |        3 |   11 | |      10 |        2 |   12 | +---------+----------+------+ 12 rows in set (0.00 sec) 

that ranks correctly users. in table lots of records, need following:

1) able small portion of list, around user ranking resides, me surrounding records, preserving overall rank: tried these things setting user variable ranking of current user , using offset , limit, couldn't preserve overall ranking.

this should me following (for instance ownerid=2 , surroundings limit 5:

+---------+----------+------+ | ownerid | quantity | rank | +---------+----------+------+ |       3 |       11 |    7 | |       6 |        6 |    8 | |       2 |        5 |    9 | --> ownerid=2 |       7 |        3 |   10 | |       1 |        3 |   11 | +---------+----------+------+ 5 rows in set (0.00 sec) 

2) i'd need query (preferably single query) gets me top 3 places + ranking of particular user id, preferably single query, no matter if he's among top 3 places or not. couldn't well

it following (for instance ownerid=2 again):

+---------+----------+------+ | ownerid | quantity | rank | +---------+----------+------+ |      11 |       73 |    1 | |       5 |       57 |    2 | |       4 |       46 |    3 | |       2 |        5 |    9 | --> ownerid=2 +---------+----------+------+ 4 rows in set (0.00 sec) 

also i'm in bit of concern performance of queries on table millions of records... hope helps :)

1) 5 entries around given id.

set @row=0; set @rk2=-1; set @id=2;  select b.* (   select a.*, @row:=@row+1 rank, if(a.ownerid=@id, @rk2:=@row, -1) rank2    (     select a.ownerid,a.quantity      tbl_items      a.classid=514)    order quantity desc) b b.rank > @rk2 - 3 limit 5; 

though you'll column rank2: want filter out explicit list of columns instead of b.*. maybe it's possible whith having clause rather nesting.

2) 3 top ranked entries + 1 specific id

select b.* (   select a.*, @row:=@row+1 rank    (     select a.ownerid,a.quantity     tbl_items      a.classid=514)   order quantity desc) b b.rank < 4 or b.ownerid=@id