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.

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