How to include a new incremented column using DENSE

In my synapse, I have a schedule table that stores information about all programs for a given day. It would be helpful if I could include rank/dense_rank in the output from this table based on the title and ordered by the event number and program date. Here are the table script and sample records. Could you please help me in achieving the What I expect column from my screenshot -

CREATE TABLE [Prod].[Schedule]
(
    [EventNo] [int] NOT NULL,
    [ProgramDate] [date] NOT NULL,
    [PlannedStartDateTime] [datetime2](3) NOT NULL,
    [PlannedEndDateTime] [datetime2](3) NOT NULL,
    [PlannedDuration] [varchar](15) NOT NULL,
    [Title] [varchar](500) NOT NULL,
    [Type] [varchar](10) NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN
);

INSERT INTO [Prod].[Schedule] VALUES(1,'2023-01-27','2023-01-27 06:00:00','2023-01-27 06:20:00','00:20:00:00','Breakfast - Episode 49','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(7,'2023-01-27','2023-01-27 06:22:00','2023-01-27 06:35:00','00:13:00:00','Breakfast - Episode 49','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(11,'2023-01-27','2023-01-27 06:37:00','2023-01-27 06:50:00','00:13:00:00','Breakfast - Episode 49','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(16,'2023-01-27','2023-01-27 06:52:00','2023-01-27 07:20:00','00:28:00:00','Breakfast - Episode 49','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(25,'2023-01-27','2023-01-27 07:23:30','2023-01-27 07:35:00','00:11:30:00','Breakfast - Episode 49','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(32,'2023-01-27','2023-01-27 07:38:30','2023-01-27 07:50:00','00:11:30:00','Breakfast - Episode 49','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(40,'2023-01-27','2023-01-27 07:53:30','2023-01-27 08:20:00','00:26:30:00','Breakfast - Episode 49','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(48,'2023-01-27','2023-01-27 08:23:30','2023-01-27 08:35:00','00:11:30:00','Breakfast - Episode 49','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(56,'2023-01-27','2023-01-27 08:38:30','2023-01-27 08:50:00','00:11:30:00','Breakfast - Episode 49','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(63,'2023-01-27','2023-01-27 08:53:30','2023-01-27 09:10:00','00:16:30:00','Breakfast - Episode 49','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(68,'2023-01-27','2023-01-27 09:13:30','2023-01-27 09:26:30','00:13:00:00','Breakfast - Episode 49','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(76,'2023-01-27','2023-01-27 09:30:00','2023-01-27 09:56:30','00:26:30:00','Briefing - Episode 336','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(84,'2023-01-27','2023-01-27 10:00:00','2023-01-27 10:20:00','00:20:00:00','Friday Morning - Episode 20','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(86,'2023-01-27','2023-01-27 10:22:00','2023-01-27 10:35:00','00:13:00:00','Friday Morning - Episode 20','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(91,'2023-01-27','2023-01-27 10:37:00','2023-01-27 10:50:00','00:13:00:00','Friday Morning - Episode 20','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(95,'2023-01-27','2023-01-27 10:52:00','2023-01-27 11:20:00','00:28:00:00','Friday Morning - Episode 20','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(97,'2023-01-27','2023-01-27 11:23:00','2023-01-27 11:35:00','00:12:00:00','Friday Morning - Episode 20','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(103,'2023-01-27','2023-01-27 11:37:00','2023-01-27 11:58:00','00:21:00:00','Friday Morning - Episode 20','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(109,'2023-01-27','2023-01-27 12:00:00','2023-01-27 12:20:00','00:20:00:00','Friday Afternoon  - Episode 12','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(111,'2023-01-27','2023-01-27 12:22:00','2023-01-27 12:35:00','00:13:00:00','Friday Afternoon  - Episode 12','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(115,'2023-01-27','2023-01-27 12:37:00','2023-01-27 12:50:00','00:13:00:00','Friday Afternoon  - Episode 12','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(118,'2023-01-27','2023-01-27 12:52:00','2023-01-27 13:20:00','00:28:00:00','Friday Afternoon  - Episode 12','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(120,'2023-01-27','2023-01-27 13:22:00','2023-01-27 13:35:00','00:13:00:00','Friday Afternoon  - Episode 12','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(123,'2023-01-27','2023-01-27 13:37:00','2023-01-27 13:58:00','00:21:00:00','Friday Afternoon  - Episode 12','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(131,'2023-01-27','2023-01-27 14:00:00','2023-01-27 14:20:00','00:20:00:00','The Briefing - Episode 62','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(133,'2023-01-27','2023-01-27 14:22:00','2023-01-27 14:35:00','00:13:00:00','The Briefing - Episode 62','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(138,'2023-01-27','2023-01-27 14:37:00','2023-01-27 14:58:00','00:21:00:00','The Briefing - Episode 62','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(142,'2023-01-27','2023-01-27 15:00:00','2023-01-27 15:20:00','00:20:00:00','Friday Show - Episode 59','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(144,'2023-01-27','2023-01-27 15:23:00','2023-01-27 15:35:00','00:12:00:00','Friday Show - Episode 59','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(148,'2023-01-27','2023-01-27 15:37:00','2023-01-27 15:50:00','00:13:00:00','Friday Show - Episode 59','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(153,'2023-01-27','2023-01-27 15:52:00','2023-01-27 16:20:00','00:28:00:00','Friday Show - Episode 59','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(155,'2023-01-27','2023-01-27 16:22:00','2023-01-27 16:35:00','00:13:00:00','Friday Show - Episode 59','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(161,'2023-01-27','2023-01-27 16:37:00','2023-01-27 16:50:00','00:13:00:00','Friday Show - Episode 59','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(165,'2023-01-27','2023-01-27 16:52:00','2023-01-27 17:20:00','00:28:00:00','Friday Show - Episode 59','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(167,'2023-01-27','2023-01-27 17:22:00','2023-01-27 17:35:00','00:13:00:00','Friday Show - Episode 59','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(172,'2023-01-27','2023-01-27 17:37:00','2023-01-27 17:58:00','00:21:00:00','Friday Show - Episode 59','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(176,'2023-01-27','2023-01-27 18:00:00','2023-01-27 18:15:00','00:15:00:00','Send to Phone - Episode 392','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(182,'2023-01-27','2023-01-27 18:17:00','2023-01-27 18:30:00','00:13:00:00','Send to Phone - Episode 392','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(186,'2023-01-27','2023-01-27 18:32:00','2023-01-27 18:45:00','00:13:00:00','Send to Phone - Episode 392','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(191,'2023-01-27','2023-01-27 18:47:00','2023-01-27 18:58:00','00:11:00:00','Send to Phone - Episode 392','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(196,'2023-01-27','2023-01-27 19:00:00','2023-01-27 19:15:00','00:15:00:00','Listen to Music  - Episode 18','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(198,'2023-01-27','2023-01-27 19:18:00','2023-01-27 19:30:00','00:12:00:00','Listen to Music  - Episode 18','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(205,'2023-01-27','2023-01-27 19:33:00','2023-01-27 19:45:00','00:12:00:00','Listen to Music  - Episode 18','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(213,'2023-01-27','2023-01-27 19:48:00','2023-01-27 19:57:00','00:09:00:00','Listen to Music  - Episode 18','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(215,'2023-01-27','2023-01-27 20:00:00','2023-01-27 20:15:00','00:15:00:00','Tonight Game - Episode 16','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(222,'2023-01-27','2023-01-27 20:18:00','2023-01-27 20:30:00','00:12:00:00','Tonight Game - Episode 16','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(229,'2023-01-27','2023-01-27 20:33:00','2023-01-27 20:45:00','00:12:00:00','Tonight Game - Episode 16','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(236,'2023-01-27','2023-01-27 20:48:00','2023-01-27 20:57:00','00:09:00:00','Tonight Game - Episode 16','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(242,'2023-01-27','2023-01-27 21:00:00','2023-01-27 21:15:00','00:15:00:00','Tonight Game - Episode 16','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(249,'2023-01-27','2023-01-27 21:18:00','2023-01-27 21:30:00','00:12:00:00','Tonight Game - Episode 16','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(256,'2023-01-27','2023-01-27 21:33:00','2023-01-27 21:45:00','00:12:00:00','Tonight Game - Episode 16','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(263,'2023-01-27','2023-01-27 21:48:00','2023-01-27 21:57:00','00:09:00:00','Tonight Game - Episode 16','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(271,'2023-01-27','2023-01-27 22:00:00','2023-01-27 22:15:00','00:15:00:00','Tonight Game - Episode 16','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(279,'2023-01-27','2023-01-27 22:18:00','2023-01-27 22:30:00','00:12:00:00','Tonight Game - Episode 16','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(286,'2023-01-27','2023-01-27 22:33:00','2023-01-27 22:45:00','00:12:00:00','Tonight Game - Episode 16','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(292,'2023-01-27','2023-01-27 22:48:00','2023-01-27 22:57:00','00:09:00:00','Tonight Game - Episode 16','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(300,'2023-01-27','2023-01-27 23:00:00','2023-01-27 23:15:00','00:15:00:00','Head - Episode 418','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(305,'2023-01-27','2023-01-27 23:17:00','2023-01-27 23:30:00','00:13:00:00','Head - Episode 418','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(310,'2023-01-27','2023-01-27 23:32:00','2023-01-27 23:45:00','00:13:00:00','Head - Episode 418','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(314,'2023-01-27','2023-01-27 23:47:00','2023-01-27 23:57:00','00:10:00:00','Head - Episode 418','LIVE');
INSERT INTO [Prod].[Schedule] VALUES(322,'2023-01-27','2023-01-27 00:00:00','2023-01-27 00:15:00','00:15:00:00','Listen to Music Replay - Episode 18','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(324,'2023-01-27','2023-01-27 00:18:00','2023-01-27 00:35:00','00:17:00:00','Listen to Music Replay - Episode 18','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(326,'2023-01-27','2023-01-27 00:38:00','2023-01-27 00:45:00','00:07:00:00','Listen to Music Replay - Episode 18','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(328,'2023-01-27','2023-01-27 00:48:00','2023-01-27 00:57:00','00:09:00:00','Listen to Music Replay - Episode 18','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(330,'2023-01-27','2023-01-27 01:00:00','2023-01-27 01:15:00','00:15:00:00','Head Replay - Episode 418','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(332,'2023-01-27','2023-01-27 01:17:00','2023-01-27 01:30:00','00:13:00:00','Head Replay - Episode 418','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(334,'2023-01-27','2023-01-27 01:32:00','2023-01-27 01:45:00','00:13:00:00','Head Replay - Episode 418','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(336,'2023-01-27','2023-01-27 01:47:00','2023-01-27 01:57:00','00:10:00:00','Head Replay - Episode 418','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(338,'2023-01-27','2023-01-27 02:00:00','2023-01-27 02:15:00','00:15:00:00','Tonight Game Replay - Episode 16','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(340,'2023-01-27','2023-01-27 02:18:00','2023-01-27 02:30:00','00:12:00:00','Tonight Game Replay - Episode 16','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(342,'2023-01-27','2023-01-27 02:33:00','2023-01-27 02:45:00','00:12:00:00','Tonight Game Replay - Episode 16','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(344,'2023-01-27','2023-01-27 02:48:00','2023-01-27 02:57:00','00:09:00:00','Tonight Game Replay - Episode 16','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(346,'2023-01-27','2023-01-27 03:00:00','2023-01-27 03:15:00','00:15:00:00','Tonight Game Replay - Episode 16','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(348,'2023-01-27','2023-01-27 03:18:00','2023-01-27 03:30:00','00:12:00:00','Tonight Game Replay - Episode 16','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(350,'2023-01-27','2023-01-27 03:33:00','2023-01-27 03:45:00','00:12:00:00','Tonight Game Replay - Episode 16','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(352,'2023-01-27','2023-01-27 03:48:00','2023-01-27 03:57:00','00:09:00:00','Tonight Game Replay - Episode 16','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(354,'2023-01-27','2023-01-27 04:00:00','2023-01-27 04:15:00','00:15:00:00','Tonight Game Replay - Episode 16','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(356,'2023-01-27','2023-01-27 04:18:00','2023-01-27 04:30:00','00:12:00:00','Tonight Game Replay - Episode 16','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(358,'2023-01-27','2023-01-27 04:33:00','2023-01-27 04:45:00','00:12:00:00','Tonight Game Replay - Episode 16','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(360,'2023-01-27','2023-01-27 04:48:00','2023-01-27 04:57:00','00:09:00:00','Tonight Game Replay - Episode 16','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(362,'2023-01-27','2023-01-27 05:00:00','2023-01-27 05:15:00','00:15:00:00','Head Replay - Episode 418','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(364,'2023-01-27','2023-01-27 05:17:00','2023-01-27 05:30:00','00:13:00:00','Head Replay - Episode 418','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(366,'2023-01-27','2023-01-27 05:32:00','2023-01-27 05:45:00','00:13:00:00','Head Replay - Episode 418','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(368,'2023-01-27','2023-01-27 05:47:00','2023-01-27 05:57:00','00:10:00:00','Head Replay - Episode 418','REPLAY');
INSERT INTO [Prod].[Schedule] VALUES(373,'2023-01-27','2023-01-27 05:59:00','2023-01-27 06:00:00','00:01:00:00','UK National Anthem - Episode 369','LIVE');


SELECT EventNo,ProgramDate,PlannedStartDateTime,PlannedEndDateTime,PlannedDuration,Title,Type,DENSE_RANK() OVER(ORDER BY Title,Type ASC) Expected
FROM [Prod].[Schedule] WHERE ProgramDate = '2023-01-27' ORDER BY EventNo,ProgramDate;

CodePudding user response：

;with cte as (
select *
    ,  case when coalesce(lag(title) over (order by EventNo), title) = Title then 0 else 1 end sameShow 
from  [Prod].[Schedule] )
select  EventNo,ProgramDate,PlannedStartDateTime,PlannedEndDateTime,PlannedDuration,Title,Type
        , sum(sameShow) over(order by EventNo)  1  expected 
from cte
order by EventNo