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

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


Result


2 comments:

  1. Good Snippet Thanks.I have some clarifications.

    1.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?

    ReplyDelete