How to generate sequence number based on specific column(Without using LOOP, CTE, #TABLE, @TABLE VARIABLE, RANKING Function)
Sample Table and record(s):
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
[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
Result
Good Snippet Thanks.I have some clarifications.
ReplyDelete1.can we achieve this using Row_Number()?
2.what do you mean a.City <= b.City?
3.Do we need have the city name in a particular order?