Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

Code Item Times
1 ABC 02:00:00.0000000
1 ABC 02:05:00.0000000
1 ABC 02:10:00.0000000
2 AC 02:25:00.0000000
2 AC 02:30:00.0000000
1 ABC 05:00:00.0000000
1 ABC 05:05:00.0000000
1 ABC 05:10:00.0000000
2 AC 06:25:00.0000000
2 AC 06:30:00.0000000
2 AC 07:25:00.0000000
2 AC 07:30:00.0000000

Expected Output

Code Item Times rowNumber
1 ABC 02:00:00.0000000 123
1 ABC 02:05:00.0000000 123
1 ABC 02:10:00.0000000 123
2 AC 02:25:00.0000000 126
2 AC 02:30:00.0000000 126
1 ABC 05:00:00.0000000 125
1 ABC 05:05:00.0000000 125
1 ABC 05:10:00.0000000 125
2 AC 06:25:00.0000000 129
2 AC 06:30:00.0000000 129
2 AC 07:25:00.0000000 129
2 AC 07:30:00.0000000 129

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

Advertisements