Ranking with repeating groups

Question

Post reply

Ranking with repeating groups

kuopaz

Default port

Points: 1449
More actions
October 5, 2021 at 1:02 pm
Go to Answer
#3936635
Hi
Input test data:
```
create table #T
(
    TestType int not null, 
    TestDate date not null primary key clustered,
    constraint T_CK check(TestType in (7, 8, 9))
)
 
insert #T(TestType, TestDate)
select 7, '2019-07-10' union all
select 7, '2019-11-14' union all
select 8, '2019-11-26' union all
select 9, '2019-11-30' union all
select 9, '2020-01-30' union all
select 8, '2020-05-14' union all
select 7, '2020-09-16'
 
select TestType, TestDate
from #T
order by TestDate

drop table #T
```
And for the output Result column to be as below. Tried playing around with window ranking functions but missing something.
TestType TestDate Result
7 2019-07-10 1
7 2019-11-14 1
8 2019-11-26 2
9 2019-11-30 3
9 2020-01-30 3
8 2020-05-14 4
7 2020-09-16 5
- This topic was modified 2 years, 6 months ago by kuopaz.
- This topic was modified 2 years, 6 months ago by kuopaz.
Mark Cowne

One Orange Chip

Points: 26944
More actions
October 5, 2021 at 1:44 pm
Answer
#3936650
```
with cte as (
select TestType, TestDate, case when lag(TestType) over(order by TestDate) = TestType then 0 else 1 end as IsStart
from #T
)
select TestType, TestDate,sum(IsStart) over(order by TestDate) as Result
from cte
order by TestDate;
```
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

October 10, 2021 at 11:54 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

Almost identical to Mark"s solution

WITH groups
AS (SELECT t.TestType
          ,t.TestDate
          ,IsNew = IIF(ISNULL(LAG(t.TestType, 1) OVER (ORDER BY t.TestDate), -1) <> t.TestType, 1, 0)
    FROM #T t)
SELECT g.TestType
      ,g.TestDate
      ,GroupNo = SUM(g.IsNew) OVER (ORDER BY g.TestDate ROWS UNBOUNDED PRECEDING)
FROM groups g
ORDER BY g.TestDate;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

kuopaz Default port Points: 1449 More actions · Answer 2

Many thanks to both of you!

I'd got bogged down in trying to use row_number and dense_rank, and didn't think about using lag.