Mojtaba Mojtaba - 3 months ago 9
PHP Question

re-order a table based on sort column and another column value

I have a table which has a

sort_no
column and the sort values are belonging to
q_id
which corresponds to question id. But, it does not include proper sorting values. Sometimes, the sort numbers are being repeated for different records with the same
q_id
. I have to refactor this table with unique sort numbers for each question.

This is a sample data I already have:

id | name | sort_no | q_id
-------------------------------
1 | val_1 | 1 | 21
2 | val_2 | 2 | 21
3 | val_3 | 1 | 32
4 | val_4 | 3 | 21
5 | val_5 | 2 | 32
6 | val_6 | 2 | 32
7 | val_7 | 1 | 25
8 | val_8 | 1 | 21
9 | val_9 | 1 | 21
-------------------------------


This is what it should be:

id | name | sort_no | q_id
------------------------------
1 | val_1 | 1 | 21
2 | val_2 | 2 | 21
3 | val_3 | 1 | 32
4 | val_4 | 3 | 21
5 | val_5 | 2 | 32
6 | val_6 | 3 | 32
7 | val_7 | 1 | 25
8 | val_8 | 4 | 21
9 | val_9 | 5 | 21
-------------------------------


Actually, I can fetch the records and put them in a loop and update it by a loop. But, as you know, it takes time and resource. The table is huge with millions of records.

I was wondering if I could do it directly in MySQL with a nested query.

I have no idea about the query.

Have anybody experienced this before?

Answer
update test5
   set sort_no=@srt:=if(@grp=q_id,@srt+1,1),
       q_id=@grp:=q_id
 where (0,0)=(select @grp:=0,@srt:=0)
 order by q_id, name

Set needed 'order by'. First column in 'order by' must be "q_id".

Comments