How to generate sequence number based on specific column(Without using LOOP, CTE, #TABLE, @TABLE VARIABLE, RANKING Function)

Pandians
Posted by Pandians under Sql Server category on | Points: 40 | Views : 2365
How to generate sequence number based on specific column(Without using LOOP, CTE, #TABLE, @TABLE VARIABLE, RANKING Function)
Sample Table and record(s):
CREATE TABLE [dbo].[AddressInfo](
[Address1] [varchar](250) NULL,
[City] [varchar](250) NULL
)
GO

INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Viejas Outlet Center', N'Alpine')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Vista Marketplace', N'Alhambra')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Wade Road', N'Basingstoke Hants')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Ward Parkway Center', N'Kansas City')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Warrington Ldc Unit 25/2', N'Woolston')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Washington Square', N'Portland')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'West Park Plaza', N'Irvine')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Wharfdale Road', N'Berkshire')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'White Mountain Mall', N'Rock Springs')
INSERT [dbo].[AddressInfo] ([Address1], [City]) VALUES (N'Wymbush', N'Milton Keynes')
Go
Generating Sequence number based on "City" column
SELECT (SELECT COUNT(1) FROM AddressInfo A WHERE a.City <= b.City) [Seq],B.* FROM AddressInfo B
ORDER BY 1
Go

Comments or Responses

Login to post response