Selecting first N rows for each group Assume I have a table like this:

+----+-----------+-----------+
| id | file_name | file_type |
+----+-----------+-----------+
|  1 | file1     | 1         |
|  2 | file2     | 1         |
|  3 | file3     | 1         |
|  4 | file4     | 2         |
|  5 | file5     | 2         |
|  6 | file6     | 2         |
|  7 | file7     | 3         |
|  8 | file8     | 3         |
|  9 | file9     | 3         |
+----+-----------+-----------+


I'm trying to get 2 rows for each file_type, so I'm following this hints:


https://blog.sqlauthority.com/2014/03/08/mysql-generating-row-number-for-each-row-using-variable/
http://www.sqlines.com/mysql/how-to/get_top_n_each_group


I'm trying the following query:

SELECT file_name, file_type,
  @type_number := IF(@current_type = file_type, @type_number + 1, 1) AS type_number,
  @current_type := file_type
FROM test
ORDER BY file_type


But I'm getting the following results:

+-----------+-----------+-------------+----------------------------+
| file_name | file_type | type_number | @current_type := file_type |
+-----------+-----------+-------------+----------------------------+
| file1     | 1         |           1 | 1                          |
| file2     | 1         |           1 | 1                          |
| file3     | 1         |           1 | 1                          |
| file4     | 2         |           1 | 2                          |
| file5     | 2         |           1 | 2                          |
| file6     | 2         |           1 | 2                          |
| file7     | 3         |           1 | 3                          |
| file8     | 3         |           1 | 3                          |
| file9     | 3         |           1 | 3                          |
+-----------+-----------+-------------+----------------------------+


As you can see the type_number column should have values from 1 to 3, but it is 1 on every rows.

Where I am wrong?
It's a little bit more complicated, you must assign each variable @type_number and @file_type again.

SELECT file_type, file_name, id
FROM (
      SELECT  @type_number := IF(@file_type = file_type, @type_number + 1, 1) AS type_number, 
              @file_type := file_type as file_type, 
              file_name, id
      FROM 
          (SELECT @type_number := 1) x, 
          (SELECT id, file_name, @file_type := file_type as file_type FROM ttest ORDER BY file_type) y
    ) z
WHERE type_number &lt;= 2;


Check it here: http://rextester.com/IUPWT52140