title: How to get this specific user rankings query in mysql?
body: <p>I've got tbl_items in my user database that I want to sort user rankings on a particular item with certain id (514). I have test data on my dev environment with this set of data:</p>
  
  <pre><code>mysql&gt; select * from tbl_items where 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)
  </code></pre>
  
  <p>so far so good :) I wrote the following query:</p>
  
  <pre><code>set @row=0;
  
  select a.*, @row:=@row+1 as rank 
  from (select a.ownerid,a.quantity from tbl_items a 
  where a.classid=514) a order by 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)
  </code></pre>
  
  <p>that ranks correctly the users. However in a table with lots of records, I need to do the following:</p>
  
  <p><strong>1) be able to get small portion of the list, around where the user ranking actually resides, something that would get me the surrounding records, preserving the overall rank:
  I tried to do these things with setting a user variable to the ranking of the current user and by using offset and limit, but couldn't preserve the overall ranking.</strong></p>
  
  <p>This should get me something like the following (for instance ownerId=2 and surroundings limit 5:</p>
  
  <pre><code>+---------+----------+------+
  | ownerid | quantity | rank |
  +---------+----------+------+
  |       3 |       11 |    7 |
  |       6 |        6 |    8 |
  |       2 |        5 |    9 | --&gt; ownerId=2
  |       7 |        3 |   10 |
  |       1 |        3 |   11 |
  +---------+----------+------+
  5 rows in set (0.00 sec)
  </code></pre>
  
  <p><strong>2) I'd also need another query (preferably single query) that gets me the top 3 places + the ranking of particular user with certain id, preferably with a single query, no matter if he's among the top 3 places or not. I couldn't get this as well</strong></p>
  
  <p>It would look like the following (for instance ownerId=2 again):</p>
  
  <pre><code>+---------+----------+------+
  | ownerid | quantity | rank |
  +---------+----------+------+
  |      11 |       73 |    1 |
  |       5 |       57 |    2 |
  |       4 |       46 |    3 |
  |       2 |        5 |    9 | --&gt; ownerId=2
  +---------+----------+------+
  4 rows in set (0.00 sec)
  </code></pre>
  
  <p>Also I'm in a bit of a concern about the performance of the queries on a table with millions of records...
  Hope someone helps :)</p>