CODEWITHSUNDEEP
sql-servert-sql
P_K
P_K

Reputation: 43

Count the number of duplicate values in row in SQL Server

I want to set number if this value is previously present in row. I want below output.

Date                        Empno           Count
-------------------------------------------------------
2018-08-01 06:00:00.000     10103612    1
2018-08-02 14:30:54.000     10103610    1
2018-08-03 06:30:00.000     10103660    1
2018-08-04 06:00:00.000     10103660    2
2018-08-04 19:15:00.000     10103610    2
2018-08-05 12:00:00.000     10103603    1
2018-08-05 17:30:00.000     10103603    2
2018-08-06 06:30:00.000     10103660    3
2018-08-06 07:00:00.000     10103531    1

ie, If Empno comes for the second or third time in list, I want to show its count in the very next column. You can see that in 10103660, it occur in the list 3 times, so the count increments for each time in list.

Upvotes: 2

Views: 155

Answers (2)

Jesse Johnson
Jesse Johnson

Reputation: 1716

ROW_NUMBER() combined with OVER() will give you a count of how many rows have the same value in the Empno column.

The following will return EmpnoCount which will tell you how many records have the same value in the Empno column as the record with the EmpnoCount:

SELECT [Date],
       [Empno],
       ROW_NUMBER() OVER (PARTITION BY [Empno] ORDER BY [Date]) AS EmpnoCount
FROM INSERT_TABLE_NAME
ORDER BY [EmpnoCount] DESC

The results should be in descending order of the most duplicated Empno value.

UPDATE #1

Working example link to SQL Fiddle that shows the results of the above query.

Upvotes: 2

Squirrel
Squirrel

Reputation: 24763

you can simply do a ROW_NUMBER() with a OVER() window function

SELECT [Date], 
       [Empno], 
       ROW_NUMBER() OVER (PARTITION BY [Empno] ORDER BY [Date]) as [Count]
FROM   yourtable
ORDER BY [Date];

Upvotes: 1

Related Questions