mysql save order within a group to the child table

Question

I have a table that has groups of records and I want to save order in the table for those records, for e.g.

==========================
group_id  |  bid  | rank  
==========================
1         |  10   |  ?
1         |  12   |  ?
2         |   4   |  ?
2         |   5   |  ?
2         |   3   |  ?
==========================

Is there some query I can use to save the rank in the above table with the order of bids, so the result becomes

==========================
group_id  |  bid  | rank  
==========================
1         |  10   |  1
1         |  12   |  2
2         |   4   |  2
2         |   5   |  3
2         |   3   |  1
==========================

Possible duplicate of [MYSQL update statement to backfill ranking by each id](https://stackoverflow.com/questions/38620515/mysql-update-statement-to-backfill-ranking-by-each-id) — Martin Schneider, Jan 12 '18 at 06:23

score 1 · Accepted Answer · answered Jan 12 '18 at 06:26

You could use a correlated sub query to get the desired rank

update demo a
join (
  select a.group_id,a.bid,
  (select count(distinct bid)
  from demo
  where a.group_id = group_id
  and a.bid > bid) + 1 rank
  from demo a
) b using(group_id,bid)
set a.rank =  b.rank

Demo

Or if you just want it for selection purpose you could use

select a.group_id,a.bid,
(select count(distinct bid)
from demo
where a.group_id = group_id
and a.bid > bid) + 1 rank
from demo a

Demo

mysql save order within a group to the child table

1 Answers1