Counting and limiting number of rows per field value not working

Question

I'm trying to limit the number of rows per field value of a given query. I've found this answered question:

As in the first answer of the link, I've created the following table:

create table mytab (
    id int not null auto_increment primary key,
    first_column int,
    second_column int
) engine = myisam;

Inserted this data:

insert into mytab (first_column,second_column) values 
    (1,1),
    (1,4),
    (2,10),
    (3,4),
    (1,4),
    (2,5),
    (1,6);

And finally run this query

select
    id,
    first_column,
    second_column,
    row_num
from 
    (select 
        *,
        @num := if(@first_column = first_column, @num + 1, 1) as row_num,
        @first_column:=first_column as c
    from mytab 
    order by first_column,id) as t,
    (select @first_column:='',@num:=0) as r;

But instead of getting this result, where the row_num increases whenever first_column is repeated,

+----+--------------+---------------+---------+
| id | first_column | second_column | row_num |
+----+--------------+---------------+---------+
|  1 |            1 |             1 |       1 |
|  2 |            1 |             4 |       2 |
|  5 |            1 |             4 |       3 |
|  7 |            1 |             6 |       4 |
|  3 |            2 |            10 |       1 |
|  6 |            2 |             5 |       2 |
|  4 |            3 |             4 |       1 |
+----+--------------+---------------+---------+

I get this result:

+----+--------------+---------------+---------+
| id | first_column | second_column | row_num |
+----+--------------+---------------+---------+
|  1 |            1 |             1 |       1 |
|  2 |            1 |             4 |       1 |
|  5 |            1 |             4 |       1 |
|  7 |            1 |             6 |       1 |
|  3 |            2 |            10 |       1 |
|  6 |            2 |             5 |       1 |
|  4 |            3 |             4 |       1 |
+----+--------------+---------------+---------+

I literally copied the code from the link. I checked in SQL Fiddle and code works fine. I'm using XAMPP. Could that be the reason? If it is, is there any workaround to get something like the above working?

I'd really appreciate some help. Thanks in advance.

score 2 · Accepted Answer · answered May 31 '17 at 19:15

2

The variable assignment has to be in the sub-query.

select
    id,
    first_column,
    second_column,
    row_num
from 
    (select 
        m.*,
        @num := if(@first_column = first_column, @num + 1, 1) as row_num,
        @first_column:=first_column as c
    from mytab m
    cross join (select @first_column:='',@num:=0) r --this was in the outer query previously
    order by first_column,id
    ) t

answered May 31 '17 at 19:15

Vamsi Prabhala

48,685
4
36
58

It works! Really appreciate it. Though I'm still puzzled by the fact that the code I copied seems to work in SQL Fiddle. In any case, thank you! – jzeta May 31 '17 at 19:19

Counting and limiting number of rows per field value not working

1 Answers1