Ranking with repeating groups

  • 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 10 months, 2 weeks ago by  kuopaz.
    • This topic was modified 10 months, 2 weeks ago by  kuopaz.
  • 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
  • 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.

  • 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.

     

  • This was removed by the editor as SPAM

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

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