CODEWITHSUNDEEP
sqlsql-serverwindow-functions
Aquen
Aquen

Reputation: 267

SQL Server - ROW_NUMBER() -> Reset again?

we are facing a specific problem in a query we're currently writing. Here is the example:

Doc. ID | Timestamp | Employee 
 01     | 01        | A        
 01     | 02        | B        
 01     | 03        | B        
 01     | 04        | C        
 01     | 05        | A        
 01     | 06        | A

What we want to achieve is that:

Doc. ID | Timestamp | Employee 
 01     | 01        | A        
 01     | 03        | B        
 01     | 04        | C        
 01     | 06        | A

This was our approach (which didn't work):

SELECT [Doc. ID], [Timestamp], [Employee]
       ,ROW_NUMBER() OVER (PARTITION BY [Doc. ID],[Employee] order by [Employee] desc) as "RN"
FROM XY
WHERE "RN" = 1

But unfortunately that doesn't work, because the Row_number does not reset when finding A again at the bottom. It The result we receive (without the where clause) is:

 Doc. ID | Timestamp | Employee | RN
  01     | 01        | A        | 1
  01     | 02        | B        | 1
  01     | 03        | B        | 2
  01     | 04        | C        | 1
  01     | 05        | A        | 2
  01     | 06        | A        | 3

I think it's only a little bit more to achieve the right solution.. :)

Upvotes: 0

Views: 80

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I think you want aggregation :

SELECT [doc. ID], MAX([Timestamp]) AS [Timestamp], employee
FROM (SELECT t.*,
             row_number() over (order by [Timestamp]) as seq1,
             row_number() over (partition by [doc. ID], employee order by [Timestamp]) as seq2
      FROM XY t
     ) t
GROUP BY [doc. ID], employee, (seq1 - seq2)
ORDER BY [Timestamp];

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Use lead() to peak at the employee value in the "next" row:

select xy.*
from (select xy.*,
             lead(employee) over (partition by docid order by timestamp) as next_employee
      from xy
     ) xy
where next_employee is null or next_employee <> employee;

Upvotes: 4

Related Questions