title: How to assign virtual auto-increment rank with limit of 4?
body: <p>Simply put, I would like rid to order my pid. </p>
  
  <p>Instead of creating an extra table called 'rank' with 4 rows I would like to be able to just attach a virtual rank that gives the same effect. My rank is just there to give a position to my tags. Since I have 4 tags per id I would have a value of 1-4 enumerating those tags. </p>
  
  <p>This way I won't have to manually put in the same numbers over and over again.</p>
  
  <p>My tables: </p>
  
  <pre>
     people            tags_rel               tags           rank
  
  id   | etc     id | pid  | tid | rid      id | tag          id
  -----+-----    ---+------+-----+----      ---+--------      --
  2345 |         1  | 2345 |  2  | 1        1  | bread        1
  2346 |         2  | 2345 |  3  | 2        2  | water        2
  2347 |         3  | 2345 |  1  | 3        3  | bear         3
                 4  | 2345 |  6  | 4        4  | milk         4
                 ---+------+-----+----      5  | hotdogs
                 5  | 2346 |  3  | 1        
                 6  | 2346 |  4  | 2
                 7  | 2346 |  2  | 3
                 8  | 2346 |  5  | 4
                 ---+------+-----+----
                 9  | 2347 |  6  | 1
                10  | 2347 |  1  | 2
                11  | 2347 |  4  | 3
                12  | 2347 |  5  | 4
                 ---+------+-----+----
  </pre>
  
  <p>My query:</p>
  
  <pre><code>SELECT p.id as pid, t.tag as tname, tr.tid as trid, r.id as rank
  FROM people AS p
  RIGHT JOIN tags_rel AS tr ON tr.pid = p.id
  LEFT JOIN tags AS t ON tr.tid = t.id
  LEFT JOIN rank AS r ON tr.rid = r.id
  </code></pre>
  
  <p>MySQL expected results:</p>
  
  <pre>
  > +--------+------------+--------+------+
  > | pid    | tname      |  trid  | rank |
  > +--------+------------+--------+------+
  > | 2345   | water      |    2   |   1  | 
  > | 2345   | bread      |    1   |   2  |
  > | 2345   | cereal     |    3   |   3  |
  > | 2345   | milk       |    4   |   4  |
  > | 2346   | cereal     |    3   |   1  |
  > | 2346   | milk       |    4   |   2  |
  > | 2346   | water      |    2   |   3  |
  > | 2346   | hotdogs    |    5   |   4  |
  > | 2347   | chocolate  |    6   |   1  | 
  > | 2347   | bread      |    1   |   2  |
  > | 2347   | bread      |    4   |   3  |
  > | 2347   | bread      |    5   |   4  |
  > +--------+------------+--------+------+
  
  </pre>
  
  <p>I am using rank as position. So whatever tags are attached to pid I would like to show by first position. Since the tags are different for each pid they would have different positions. I may want to call tname=water by rank=1 and it would display all the 'water' tags in position 1 only.</p>
  
  <p>Thanks!</p>