Search code examples
sql-servert-sqlgroup-bydense-rank

T-SQL - Adding a numerical grouping

I'm needing to add a numerical grouping or ranking to a set of data to later be used. I'm struggling to get the group numbers how I need them.

Here's some sample data:

create table #TVShow (
    TV varchar(20),
    Show varchar(20),
    Channel varchar(20),
    [Time] time)

insert into #TVShow
values ('Samsung', 'The Office', 'Bravo', '07:00:00'),
       ('Samsung', 'The Office', 'Bravo', '07:30:00'),
       ('Samsung', 'The Office', 'Bravo', '08:00:00'),
       ('Samsung', 'The Office', 'Bravo', '08:30:00'),
       ('Samsung', 'The Office', 'TBS', '09:00:00'),
       ('Samsung', 'The Office', 'TBS', '09:30:00'),
       ('Samsung', 'The Office', 'MTV', '10:00:00'),
       ('Samsung', 'The Office', 'Bravo', '10:30:00'),
       ('Samsung', 'The Office', 'Bravo', '11:00:00'),
       ('Samsung', 'The Office', 'TBS', '11:30:00'),

       ('Samsung', 'Below Deck', 'TBS', '07:00:00'),
       ('Samsung', 'Below Deck', 'TBS', '07:30:00'),
       ('Samsung', 'Below Deck', 'Bravo', '09:00:00'),
       ('Samsung', 'Below Deck', 'Bravo', '09:30:00'),
       ('Samsung', 'Below Deck', 'Discovery', '10:00:00'),

       ('LG', 'The Office', 'Bravo', '07:00:00'),
       ('LG', 'The Office', 'Bravo', '07:30:00'),
       ('LG', 'The Office', 'Bravo', '08:00:00'),
       ('LG', 'The Office', 'Bravo', '08:30:00'),
       ('LG', 'The Office', 'TBS', '09:00:00'),
       ('LG', 'The Office', 'TBS', '09:30:00'),
       ('LG', 'The Office', 'MTV', '10:00:00'),
       ('LG', 'The Office', 'Bravo', '10:30:00'),
       ('LG', 'The Office', 'Bravo', '11:00:00'),
       ('LG', 'The Office', 'TBS', '11:30:00'),

       ('LG', 'Below Deck', 'TBS', '07:00:00'),
       ('LG', 'Below Deck', 'TBS', '07:30:00'),
       ('LG', 'Below Deck', 'Bravo', '09:00:00'),
       ('LG', 'Below Deck', 'Bravo', '09:30:00'),
       ('LG', 'Below Deck', 'Discovery', '10:00:00')

Here is my desired outcome

desired outcome

I'm looking to group by TV, Show and Channel in [Time] ascending order. Each group (starting at 1) will increment by 1 when the Channel changes, and will restart the count back at 1 if the Show and/or TV changes.

I've tried several variations of dense_rank(), for example,

select TV,
    Show,
    Channel,
    [Time],
    Num = dense_rank() over (partition by TV, Show, Channel order by TV, Show, [Time])
from #TVShow

But cannot get it to how I'm needing it. Any help would be great. Thanks!

Solution

  • If I am assuming your logic correct, you want to increment the value each time the channel changes for a specific show and TV. One method would be to use LAG to get the value of the prior row, and then a windowed cumulative condition COUNT:

    WITH CTE AS(
    SELECT TV,
           Show,
           Channel,
           [Time],
           LAG(Channel) OVER (PARTITION BY TV, Show ORDER BY Time) AS PreviousChannel
    FROM #TVShow)
SELECT TV,
       Show,
       Channel,
       [Time],
       COUNT(CASE WHEN Channel != PreviousChannel THEN 1 END) OVER (PARTITION BY TV, Show
                                                                    ORDER BY [Time] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) +1 AS Num
FROM CTE;