SQL - ROW_NUMBER()

-- ROW_NUMBER function

DECLARE @DEMO TABLE

(

   Name  VARCHAR(15),

   ADDR1 VARCHAR(50),

   ADDR2 VARCHAR(50),

   CITY  VARCHAR(50),

   ZIP   CHAR(6)

)

-- Insert testing records

INSERT INTO @DEMO VALUES

('Zhang San','Leshan Road 33','','Shanghai','200081'),

('Li Si','Zhaojiabang Road 23','Nan song Street 9002','Shanghai','200083'),

('Wang Wu','Huashan Road 27','Weihai zhong Road 1039','Shanghai','200081'),

('Sun liu','Jiaoda dong Round 12','Gao liangqiao xiejie 19','Beijing','100023'),

('Yang Qi','Zhong guan cun nan A39','','Beijing','100009'),

('Zhu Ba','Cuihua nan 30','haiding road 32','Beijing','100103'),

('Huang Jiu','Lv you Road 33','Huan dong jie 39','Wuhan','420021')

-- Original query

SELECT *

FROM @DEMO

-- Only to set row number for each record.

SELECT ROW_NUMBER() OVER(ORDER BY addr.city) AS 'ID',

       addr.Name,

       addr.ADDR1,

       addr.CITY,

       addr.ZIP

FROM @DEMO AS addr

-- Set group first, then set row number for each record under the group - city

SELECT ROW_NUMBER() OVER (PARTITION BY addr.CITY

                          ORDER BY addr.CITY) AS 'ID',

       addr.Name,

       addr.ADDR1,

       addr.CITY,

       addr.ZIP

FROM @DEMO AS addr

-- Set group first, then set row number for each record under the group - city and zip

SELECT ROW_NUMBER() OVER (PARTITION BY addr.CITY,

                                       addr.ZIP

                          ORDER BY addr.CITY) AS 'ID',

       addr.Name,

       addr.ADDR1,

       addr.CITY,

       addr.ZIP

FROM @DEMO AS addr