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

Vivek Komarla Bhaskar 911 Reputation points
2023-01-27T16:37:36.06+00:00

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 -

User's image

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
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,108 questions
{count} votes