tsql requirement

Question

Post reply

tsql requirement

vsamantha35

SSChampion

Points: 11529
More actions
November 10, 2017 at 6:03 am

#341211

Hi All,
Need some help in tsql.
I want to have a new generated column (i.e. Txnid), for every 1..6 records, i want a new id to be assigned.
And Also rows with id = 7 should be ignored. ( i mean excluded in the output).
I have attached expected output as a screenshot.
CREATE TABLE [dbo].[test](
   [id] [int] NULL,
   [c1] [varchar](100) NULL,
   [c2] [varchar](100) NULL,
   [c3] [varchar](100) NULL
)
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (1, N'A', N'A', N'A')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (2, N'A', N'A', N'A')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (3, N'A', N'A', N'A')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (4, N'A', N'A', N'A')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (5, N'A', N'A', N'A')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (6, N'A', N'A', N'A')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (7, N'A', N'A', N'A')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (1, N'B', N'B', N'B')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (2, N'B', N'B', N'B')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (3, N'B', N'B', N'B')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (4, N'B', N'B', N'B')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (5, N'B', N'B', N'B')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (6, N'B', N'B', N'B')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (7, N'B', N'B', N'B')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (1, N'C', N'C', N'C')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (2, N'C', N'C', N'C')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (3, N'C', N'C', N'C')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (4, N'C', N'C', N'C')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (5, N'C', N'C', N'C')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (6, N'C', N'C', N'C')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (1, N'D', N'D', N'D')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (2, N'D', N'D', N'D')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (3, N'D', N'D', N'D')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (4, N'D', N'D', N'D')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (5, N'D', N'D', N'D')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (6, N'D', N'D', N'D')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (1, N'E', N'E', N'E')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (2, N'E', N'E', N'E')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (3, N'E', N'E', N'E')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (4, N'E', N'E', N'E')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (5, N'E', N'E', N'E')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (6, N'E', N'E', N'E')
GO
INSERT [dbo].[test] ([id], [c1], [c2], [c3]) VALUES (7, N'E', N'E', N'E')
GO
Thanks,
Sam

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

SELECT DENSE_RANK() OVER (ORDER BY C1) AS Txnid , id , c1 , c2 , c3 FROM dbo.test WHERE id < 7;

John

vsamantha35 SSChampion Points: 11529 More actions · Answer 2

John Mitchell-245523 - Friday, November 10, 2017 7:09 AM
SELECT DENSE_RANK() OVER (ORDER BY C1) AS Txnid , id , c1 , c2 , c3 FROM dbo.test WHERE id < 7;
John

Thank you John.