0

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:

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?

2

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 <= 2;

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

  • I'm trying with the following query (you missed a comma), but I'm getting an error: SELECT file_type, file_name 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 FROM (@type_number := 1) x, (SELECT file_name, @file_type := file_type as file_type FROM test ORDER BY file_type) y ) z WHERE type_number <= 2; Error: [...] near '@type_number := 1) x, (SELECT file_name, @file_type := file_type as file' at line 8 – Mat Feb 1 '17 at 12:18
  • McNets, could you help me please? – Mat Feb 1 '17 at 12:37
  • Missing SELECT in (SELECT @type_number := 1) x, – McNets Feb 1 '17 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.