CODEWITHSUNDEEP
t-sqlsql-server-2012
poshan
poshan

Reputation: 3287

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

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

Upvotes: 0

Views: 4167

Answers (1)

anon
anon

Reputation:

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.

Upvotes: 2

Related Questions