CODEWITHSUNDEEP
mysqlsqlduplicates
bwoebi
bwoebi

Reputation: 23777

Incrementing counter on each duplicate in column

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...?

Upvotes: 3

Views: 2755

Answers (4)

Yair I
Yair I

Reputation: 1133

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;

Upvotes: 0

tooba jalali
tooba jalali

Reputation: 86

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:)

Upvotes: 0

Alma Do
Alma Do

Reputation: 37365

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

Upvotes: 6

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

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

Upvotes: 2

Related Questions