SQL Puzzle | The Keys Puzzle

You have to write a query that will give us row numbers for the table

For details please check out the sample input and the expected output below-

Sample Inputs

Expected Output

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data

--

CREATE TABLE [dbo].[Keys](
	[Code] [tinyint] NULL,
	[Item] [varchar](3) NULL,
	[Times] [time](7) NULL
) ON [PRIMARY]

INSERT [dbo].[Keys] ([Code], [Item], [Times]) VALUES (1, N'ABC', CAST(N'02:00:00' AS Time))
INSERT [dbo].[Keys] ([Code], [Item], [Times]) VALUES (1, N'ABC', CAST(N'02:05:00' AS Time))
INSERT [dbo].[Keys] ([Code], [Item], [Times]) VALUES (1, N'ABC', CAST(N'02:10:00' AS Time))
INSERT [dbo].[Keys] ([Code], [Item], [Times]) VALUES (2, N'AC', CAST(N'02:25:00' AS Time))
INSERT [dbo].[Keys] ([Code], [Item], [Times]) VALUES (2, N'AC', CAST(N'02:30:00' AS Time))
INSERT [dbo].[Keys] ([Code], [Item], [Times]) VALUES (1, N'ABC', CAST(N'05:00:00' AS Time))
INSERT [dbo].[Keys] ([Code], [Item], [Times]) VALUES (1, N'ABC', CAST(N'05:05:00' AS Time))
INSERT [dbo].[Keys] ([Code], [Item], [Times]) VALUES (1, N'ABC', CAST(N'05:10:00' AS Time))
INSERT [dbo].[Keys] ([Code], [Item], [Times]) VALUES (2, N'AC', CAST(N'06:25:00' AS Time))
INSERT [dbo].[Keys] ([Code], [Item], [Times]) VALUES (2, N'AC', CAST(N'06:30:00' AS Time))
INSERT [dbo].[Keys] ([Code], [Item], [Times]) VALUES (2, N'AC', CAST(N'07:25:00' AS Time))
INSERT [dbo].[Keys] ([Code], [Item], [Times]) VALUES (2, N'AC', CAST(N'07:30:00' AS Time))
GO

--

—

SOLUTION – 1 | Using Row_Number()

--

SELECT 
	 Code
	,Item
	,Times
	,123 + row_number() over (order by times) - row_number() over (partition by code, item order by times) rw 
FROM keys 
ORDER BY times 

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com