CODEWITHSUNDEEP
sql
CHG
CHG

Reputation: 3

How to apply a count for repeating rows

I want to be able to sequentially count each matching row and start back at 1 for the next set of matching rows. So far I have only been able to sequentially count rows, I have not worked out how to reset the count when a new set of values appear

This is my SQL so far:

SELECT row_number() OVER (ORDER BY PersonId,ProductID) SequenceCount
        ,PersonId AS [Person ID]
        ,ProductID AS [Product ID]
        ,fullname_a AS [Full Name] 
FROM Table_A

I would like the SequenceCount to replicate DesiredOutcome (i.e. for every PersonID and ProductID combination that are the same count 1,2,etc then when the combination values changes to the next the count restarts at 1,2, etc again. e.g.

sample data plus desired outcome in far right column

Upvotes: 0

Views: 64

Answers (3)

Ashok Gadri
Ashok Gadri

Reputation: 518

You can do it by using partition by

Try this:

SELECT PersonId AS "Person ID"
    ,ProductID AS "Product ID"
    ,full_name_a AS "Full Name" 
    , row_number() OVER (PARTITION BY PersonID ORDER BY PersonId,ProductID) SequenceCount
FROM table_a

Demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You want partition by. I think that would be:

SELECT row_number() OVER (PARTITION BY PersonId, ProductID ORDER BY PersonId) as SequenceCount

This will enumerate the rows for each person/product combination. Within each combination, the resulting ordering is arbitrary. You can adjust that by changing the order by column.

Upvotes: 2

Renat
Renat

Reputation: 8962

Looks like you need:

SELECT row_number() OVER (PARTITION BY ProductID ORDER BY PersonId) SequenceCount 
    ...

Upvotes: 1

Related Questions