Incrementing counter on each duplicate in column

Question

I have a table:

id | value
1  | -
1  | a
2  | b
1  | c
3  | d
2  | e

then I need a counter column which begins from 1 for every different value in the id column

Desired select result:

id | value | counter
1  | -     | 1
1  | a     | 2
2  | b     | 1
1  | c     | 3
3  | d     | 1
2  | e     | 2

All I found was for counting the number of duplicates etc., but not for an incrementing counter on each duplicate of a specific column...?

This is do-able with some hacks in MySQL, but IMO you should consider doing it in the app instead. — Vatev, Apr 07 '14 at 12:14
@Vatev Actually it's some query in the init file configured in the my.cnf to initialize a memory table for quicker access; I can't really use a separate script for that? — bwoebi, Apr 07 '14 at 12:16
@RonaldAlexanderKailola the id is static (that memory table is completely regenerated at every change; the cron runs once a day) — bwoebi, Apr 07 '14 at 12:23

score 6 · Accepted Answer · answered Apr 07 '14 at 12:21

6

If you do not care about ordering, only about corresponding row number, use variables like this:

SELECT 
  t.*, 
  @i:=IF(id=@id, @i+1, 1) AS num, 
  @id:=id 
FROM 
  t 
  CROSS JOIN (SELECT @i:=0, @id:=0) AS init 
ORDER BY id

answered Apr 07 '14 at 12:21

Alma Do

37,009
9
76
105

This will work, yes - but `FULL SACN` is guaranteed on table without indexing. Should be faster than `JOIN`, however – Alma Do Apr 07 '14 at 12:28
Taking less than a second on a big table, it's perfect :-) – bwoebi Apr 07 '14 at 12:53
And if you care about ordering you can wrap this in another SELECT and order by whatever. – Vatev Apr 07 '14 at 12:57
@Vatev yup, that's what I'm doing. – bwoebi Apr 07 '14 at 12:58

score 2 · Answer 2 · answered Apr 07 '14 at 12:19

2

What you are trying to accomplish is called RANKING . Unfortunately MySQL doesnot have ranking functions like in SQL Server ROW_NUMBER() , DENSE_RANK() etc..

You can try below query for MySQL

SELECT t.ID, t.Value, count(*) as Counter 
FROM tableName t
JOIN tableName b ON t.ID = b.ID AND t.Value >= b.Value
GROUP BY t.ID, t.Value

SQL Fiddle DEMO

answered Apr 07 '14 at 12:19

Mudassir Hasan

28,083
20
99
133

Nice, just one little problem: It's not yet finished after 5 minutes on a 20k rows table; do you have something a bit faster eventually? – bwoebi Apr 07 '14 at 12:24
Sorry , I work on SQL Server , don't have much knowledge of MySQL :( – Mudassir Hasan Apr 07 '14 at 12:25
1

Without proper indexing this _will be slow_ because of how `JOIN` works with specified condition – Alma Do Apr 07 '14 at 12:27
BTW have done indexing on columns.. ? If not , see doing it as Indexing considerably improves performance when Joins are used – Mudassir Hasan Apr 07 '14 at 12:27
This will be slow regardless, I'm afraid. It just doesn't scale so well. – Strawberry Apr 07 '14 at 12:29
better, I now took only 3 minutes… :-D I'll try the other answer too now. – bwoebi Apr 07 '14 at 12:36
1

The join will get slower with more repeating ID's. The session var approach (@AlmaDo's answer) will always be faster. – Vatev Apr 07 '14 at 13:03

score 0 · Answer 3 · answered Apr 07 '14 at 12:28

0

select  t.id1,  t.value,
row_number()  over (partition by t.id1 order by t.value ) counter
from test t;

I hope this will be useful:)

answered Apr 07 '14 at 12:28

tooba jalali

86
6

^ I found that one already, but I have mysql (look at the tags). – bwoebi Apr 07 '14 at 12:29
This is nice query for `Oracle` and `SQL Server`. – Hamidreza Apr 07 '14 at 12:32
well,probably it would be better to delete sql tag.in addition to,perhaps this link would be useful to find equivalent solution in MySQL:http://stackoverflow.com/questions/11963818/row-number-equivalent-in-mysql-for-inserting – tooba jalali Apr 07 '14 at 12:53

Yair I · Answer 4 · 2014-04-07T12:53:49.207

0

This will do the work for you:

SELECT [id]
      ,[value]
      ,ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY value) AS 'counter'
  FROM [TableName]

ROW_NUMBER will add the numbering. The PARTITION BY clause allows us to group the results within the call to ROW_NUMBER() without grouping them ourselves via a GROUP BY. It just tells the ROW_NUMBERR what groupings to use when it does its counting.

For MySql you can use those links:

http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

http://www.folkstalk.com/2013/03/grouped-row-number-function-mysql.html

In your case:

SET @row_number:=0;
SET @id:='';
SELECT @row_number:=CASE WHEN @id=id THEN @row_number+1 ELSE 1 END AS row_number , @id:=id AS id, value
FROM TableName
ORDER BY id;

edited Apr 07 '14 at 12:53

answered Apr 07 '14 at 12:31

Yair I

1,133
1
6
9

2

Question is tagged `mysql`, isn't it? – Alma Do Apr 07 '14 at 12:32
1

This is wrong reason to answer with sql-server query because it's specific syntax which _won't work_ for mysql. So, despite it has linked to sql, it's still not correct to answer with non-working syntax for specific-tagged questions (in short: `mysql` overrides `sql`) – Alma Do Apr 07 '14 at 12:44

Incrementing counter on each duplicate in column

4 Answers4