title: MySQL row variable out of order
body: <p>I have these 2 MySQL tables:</p>
  
  <pre><code>DESCRIBE time;
  +---------------+-------------------------------------+------+-----+---------+----------------+
  | Field         | Type                                | Null | Key | Default | Extra          |
  +---------------+-------------------------------------+------+-----+---------+----------------+
  | id            | int(11)                             | NO   | PRI | NULL    | auto_increment |
  | date          | date                                | NO   |     | NULL    |                |
  +---------------+-------------------------------------+------+-----+---------+----------------+
  
  DESCRIBE employee;
  +---------+--------------------------------------+------+-----+---------+----------------+
  | Field   | Type                                 | Null | Key | Default | Extra          |
  +---------+--------------------------------------+------+-----+---------+----------------+
  | id      | int(11)                              | NO   | PRI | NULL    | auto_increment |
  | tid     | int(11)                              | NO   | MUL | NULL    |                |
  | salary  | double                               | NO   |     | NULL    |                |
  | age     | int(11)                              | NO   |     | NULL    |                |
  +---------+--------------------------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>In the query below, I'm attempting to attach row number for my [salary | date] table. The row number gets reset to 1 as the date changes. Here's my query:</p>
  
  <pre><code>SELECT IF(@prev != d.date, @rownum := 1, @rownum := @rownum + 1) AS row, @prev := d.date, e.salary
  FROM employee e, time d, (SELECT @rownum := 0, @prev := NULL) r
  WHERE e.age &gt;= 18 AND e.age &lt;= 25 AND e.tid = d.id AND d.date &gt;= '2002-01-01' AND d.date &lt;= '2003-01-01'
  ORDER BY date, salary;
  </code></pre>
  
  <p>I got the results as shown in the first table below. The row numbers are incorrect. The expected row numbers are shown in the second table below.</p>
  
  <pre><code>+------+-----------------+---------+
  | row  | @prev := d.date | salary  |
  +------+-----------------+---------+
  |    6 | 2002-01-01      | 21823.2 |
  |    2 | 2002-01-01      |   27279 |
  |    9 | 2002-01-01      |   35073 |
  |    8 | 2002-01-01      | 36631.8 |
  |    4 | 2002-01-01      | 36631.8 |
  |    7 | 2002-01-01      |   38970 |
  |    5 | 2002-01-01      | 52219.8 |
  |    3 | 2002-01-01      |  350730 |
  |    1 | 2002-01-01      |  389700 |
  |    7 | 2003-01-01      | 21823.2 |
  |    2 | 2003-01-01      |   27279 |
  |    4 | 2003-01-01      | 31565.7 |
  |   10 | 2003-01-01      |   35073 |
  |    3 | 2003-01-01      | 36242.1 |
  |    9 | 2003-01-01      | 36631.8 |
  |    5 | 2003-01-01      | 36631.8 |
  |    8 | 2003-01-01      |   38970 |
  |    6 | 2003-01-01      | 52219.8 |
  |    1 | 2003-01-01      |  389700 |
  +------+-----------------+---------+
  </code></pre>
  
  <p>I expected the following results with correct row numbers. Thoughts?</p>
  
  <pre><code>+------+-----------------+---------+
  | row  | @prev := d.date | salary  |
  +------+-----------------+---------+
  |    1 | 2002-01-01      | 21823.2 |
  |    2 | 2002-01-01      |   27279 |
  |    3 | 2002-01-01      |   35073 |
  |    4 | 2002-01-01      | 36631.8 |
  |    5 | 2002-01-01      | 36631.8 |
  |    6 | 2002-01-01      |   38970 |
  |    7 | 2002-01-01      | 52219.8 |
  |    8 | 2002-01-01      |  350730 |
  |    9 | 2002-01-01      |  389700 |
  |    1 | 2003-01-01      | 21823.2 |
  |    2 | 2003-01-01      |   27279 |
  |    3 | 2003-01-01      | 31565.7 |
  |    4 | 2003-01-01      |   35073 |
  |    5 | 2003-01-01      | 36242.1 |
  |    6 | 2003-01-01      | 36631.8 |
  |    7 | 2003-01-01      | 36631.8 |
  |    8 | 2003-01-01      |   38970 |
  |    9 | 2003-01-01      | 52219.8 |
  |   10 | 2003-01-01      |  389700 |
  +------+-----------------+---------+
  </code></pre>