CODEWITHSUNDEEP
sqlsql-servert-sql
stepheniok
stepheniok

Reputation: 405

How can I count each row with duplicate entries in SQL server?

I'm attempting to create a column DisplayOrder which will increment by 1 anytime there is a duplicate entry in IdTitle_Code

I've attempted to use COUNT() but the end result has been every row has a count of "1".

How can I increment the count by 1 anytime there are duplicate entries? Here is an example of my desired output:

ReportId ReportTitle IdTitle_Code DisplayOrder
34 Test Report Title 21 1
21 Test Report Title 2 7 1
3 Test Report Title 3 21 2
13 Test Report Title 4 21 3
8 Test Report Title 5 11 1

So in this example every time "IdTitle_Code" is 21 the DisplayOrder count goes +1. This would go for any duplicate entry, if there was another IdTitle_Code of 7 then the next entry would be +1.

Here is a code snippet:

CREATE TABLE TableOne
(
    ReportId INT,
    ReportTitle VARCHAR(100),
    IdTitle_Code INT,

)

INSERT INTO TableOne (ReportId,ReportTitle,IdTitle_Code)
VALUES (34,'Test Report Title',21),(21,'Test Report Title 2', 7), (3,'Test Report Title 3',21), (13,'Test Report Title 4',21) , (8,'Test Report Title 5',11) 



SELECT  ReportId, 
        ReportTitle, 
        IdTitle_Code,
        COUNT(IdTitle_Code) as DisplayOrder

FROM    TableOne
GROUP BY ReportId, ReportTitle, IdTitle_Code

This is my current result from the code snippet:

ReportId ReportTitle IdTitle_Code DisplayOrder
34 Test Report Title 21 1
21 Test Report Title 2 7 1
3 Test Report Title 3 21 1
13 Test Report Title 4 21 1
8 Test Report Title 5 11 1

All columns are returning 1. How can I get the DisplayOrder column to +1 anytime it's a new entry with the same IdTitle_Code?

Upvotes: 1

Views: 1263

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Just use row_number():

select t.*,
       row_number() over (partition by idTitle_code order by reportTitle) as displayOrder
from tableone t;

Note that this requires one or more columns to define the ordering. Based on your sample data, this uses reportTitle.

Upvotes: 1

Related Questions