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,365 questions
How to include a new incremented column using DENSE_RANK() in Synapse
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(278.4, 53%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EVK%3C/text%3E%3C/svg%3E)
Vivek Komarla Bhaskar
911
Reputation points
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
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(192, 26%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHM%3C/text%3E%3C/svg%3E)
HimanshuSinha-msft 19,376 Reputation points Microsoft Employee2023-01-31T21:56:35.6633333+00:00 Hello @Vivek Komarla Bhaskar ,
Thanks for the question and using MS Q&A platform.I did try to look into the data from different perspective and i do not think at least to me we can have the data what you are looking for with a query. The reason being some of the logic works in one case but contradicts in the other example, if we focus on the episode # for #1 and #2, then same logic will not work on #3 and #4 (the order is reversed)
What is the logic for what you are expecting? Can you please explain that in words ?
Thanks
Himanshu -
HimanshuSinha-msft 19,376 Reputation points Microsoft Employee
2023-02-15T04:33:51.13+00:00 Hello @Vivek Komarla Bhaskar ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu
Sign in to comment