MySQL: Count all duplicates and update additional column with acs numbers relative to the duplicated values

Question

Slightly complicated question.

I have table below:

UserID | Date       |
---------------------
1      | 2015/01/01 |
1      | 2015/01/02 |
1      | 2015/01/03 |
1      | 2015/01/04 |
2      | 2015/01/01 |
2      | 2015/01/02 |
3      | 2015/01/01 |
3      | 2015/01/02 |
3      | 2015/01/03 |
4      | 2015/01/01 |
4      | 2015/01/02 |
4      | 2015/01/03 |
4      | 2015/01/04 |

I would like to count all duplicates and then update DupOrder column with numbers representing each duplicated UserID in the same order as date (I hope this make sense).

Here is what I would like to end up with:

DupOrder | UserID | Date       |
--------------------------------
1        | 1      | 2015/01/01 |
2        | 1      | 2015/01/02 |
3        | 1      | 2015/01/03 |
4        | 1      | 2015/01/04 |
1        | 2      | 2015/01/01 |
2        | 2      | 2015/01/02 |
1        | 3      | 2015/01/01 |
2        | 3      | 2015/01/02 |
3        | 3      | 2015/01/03 |
1        | 4      | 2015/01/01 |
2        | 4      | 2015/01/02 |
3        | 4      | 2015/01/03 |
4        | 4      | 2015/01/04 |

I use mysql.

In MySQL it can be done with [in-line assignments](http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/). — PM 77-1, Jan 01 '16 at 22:26
@PM 77-1 looks like this might be the answer I was looking for. I need to test it first. Thank you! — Chriser, Jan 01 '16 at 22:38
Since MySQL does not support analytical functions, using variables seems like the only choice. — PM 77-1, Jan 01 '16 at 22:43
This isn't a duplicate count, it's the rank within the group. — Barmar, Jan 01 '16 at 23:20

MySQL: Count all duplicates and update additional column with acs numbers relative to the duplicated values

0 Answers0