CODEWITHSUNDEEP
sql-servert-sql
jdids
jdids

Reputation: 571

Getting ROW_NUMBER to repeat if field meets a condition

I need ROW_NUMBER to assign data to a specific user if a condition is met.

ROW_NUMBER will increment normally until a duplicate value is found. When the duplicate value is found, I need it to use the same ROW_NUMBER until a new value is found.

For instance...

When using 

SELECT ROW_NUMBER() OVER (ORDER BY COMPANY) AS rownum
      ,Company
      ,Contact
  FROM TABLE

We can obviously expect this result

rownum     Company              Contact
     1     BOB'S BURGERS        BOB
     2     STEVE'S SARDINES     STEVE
     3     STEVE'S SARDINES     JERRY
     4     STEVE'S SARDINES     MARY
     5     LARRY's LOBSTER      LARRY
     6     CHRIS' COWS          CHRIS

What I'm trying to get is this. Whenever the Company name doesn't change, repeat the ROW_NUMBER and continue to increment the number when the company does change

rownum     Company              Contact
     1     BOB'S BURGERS        BOB
     2     STEVE'S SARDINES     STEVE
     2     STEVE'S SARDINES     JERRY
     2     STEVE'S SARDINES     MARY
     3     LARRY'S LOBSTER      LARRY
     4     CHRIS' COWS          CHRIS

I'm using this condition to see if the company matches the previous company name. It returns a 2 if the condition is true

ROW_NUMBER() OVER (PARTITION BY COMPANY ORDER BY COMPANY) AS SameCompany

Upvotes: 0

Views: 1986

Answers (1)

Steve Dowling
Steve Dowling

Reputation: 1999

You want DENSE_RANK not ROW_NUMBER. Try this:

SELECT DENSE_RANK() OVER (ORDER BY COMPANY) AS rownum
      ,Company
      ,Contact
  FROM TABLE

Upvotes: 8

Related Questions