How to use Row_Number() Function in SQL Server 2012?

Question

I am trying to generate result set similar in the following table. However, could not achieve the goal. I want to assign each row of the table as shown in the 'I want' column of the following table. Following SQL generated 'RowNbr' column. Any suggestion would be appreciated. Thank you

SELECT Date, Nbr, status, ROW_NUMBER () over (partition by Date,staus  order by date asc) as RowNbr

enter image description here

Thank you

Is Nbr always the same, or are you always filtering to a single Nbr? If not, could you indicate what you want when Nbr is different (particularly if there are multiple different Nbr values for the same day)? — Aaron Bertrand
– Aaron Bertrand, Commented Jan 28, 2014 at 18:46
Hi Aaron, like you said, I am working on a filtered Nbr to get the result I want. I was hoping to replicate the same logic when it comes to the multiple numbers. Any suggestion? — poshan
– poshan, Commented Jan 28, 2014 at 19:05
Okay, just FYI, partitioning by date doesn't really help you, since that starts the counter over for every new date, which is every new row in this case (I don't think the RowNbr in your screen shot was generated by the ROW_NUMBER() expression in your code above). — Aaron Bertrand
– Aaron Bertrand, Commented Jan 28, 2014 at 19:21
You are right, if I include both Date and status, I would get 1 in each row. Above chart was generated using partition by status. Thanks though — poshan
– poshan, Commented Jan 29, 2014 at 17:43

Aaron Bertrand · Accepted Answer · 2014-01-28 19:25:19Z

2

This is a classic "gaps and islands" problem, in case you are searching for similar solutions in the future. Basically you want the counter to reset every time you hit a new status for a given Nbr, ordered by date.

This general overall technique was developed, I believe, by Itzik Ben-Gan, and he has tons of articles and book chapters about it.

;WITH cte AS
(
  SELECT [Date], Nbr, [Status], 
    rn = ROW_NUMBER() OVER (PARTITION BY Nbr ORDER BY [Date]) 
       - ROW_NUMBER() OVER (PARTITION BY Nbr,[Status] ORDER BY [Date])
    FROM dbo.your_table_name
)
SELECT [Date], Nbr, [Status], 
  [I want] = ROW_NUMBER() OVER (PARTITION BY Nbr,rn ORDER BY [Date]) 
FROM cte
ORDER BY Nbr, [Date];

On 2012, you may be able to achieve something similar using LAG and LEAD; I made a few honest attempts but couldn't get anywhere that would end up being anything less complex than the above.

edited Jan 28, 2014 at 19:25

answered Jan 28, 2014 at 19:15

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

poshan Over a year ago

Smart! Thats all I was looking for. Thank you

Collectives™ on Stack Overflow

How to use Row_Number() Function in SQL Server 2012?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related