How to include a new incremented column using DENSE_RANK() in Synapse

Question

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;

Horaciux · Accepted Answer · 2023-01-28T19:33:52.227

0

;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

edited Jan 28 '23 at 19:33

answered Jan 27 '23 at 17:23

Horaciux

6,322
2
22
41

I appreciate your help! SELECT will not work for the following: 1. As the CTE grouping is based only on Title, this query would not work if you had the same data for multiple ProgramDates. 2. Here, the title 'Head Replay - Episode 418' repeats at different hours, so they should be ranked differently. Despite the events occurring out of order, they have the same ranking. – Vivek KB Jan 28 '23 at 00:12
see my edit, now groping by date and hour – Horaciux Jan 28 '23 at 02:00
Hello, I think I'm not clear. Take a look at my screenshot and the column 'What I expect'. The ranking should be grouped by Title and continuous events. There are multiple replays of Head Replay - Episode 418 throughout the day and the events are not continuous. As a result, each group should receive a different ranking. The query should give me results exactly as my screenshot indicates for the column 'What I expect' – Vivek KB Jan 28 '23 at 08:49
See my edit, now it match your expected results – Horaciux Jan 28 '23 at 19:34

How to include a new incremented column using DENSE_RANK() in Synapse

1 Answers1