Auto generated Numbers in SQL Query

Auto generated Numbers in SQL Query

create table CountryMaster
(
id int identity(1,1),
cityname varchar(50),
countryname varchar(50)
)

select * from CountryMaster

insert into CountryMaster values('New Delhi','India')
insert into CountryMaster values('Mumbai','India')
insert into CountryMaster values('Kolkata','India')
insert into CountryMaster values('Chennai','India')

insert into CountryMaster values('London','UK')
insert into CountryMaster values('Amsterdam','UK')
insert into CountryMaster values('Southampton','UK')

insert into CountryMaster values('Washington','US')
insert into CountryMaster values('New York','US')
insert into CountryMaster values('Chicago','US')

insert into CountryMaster values('Canberra','Australia')
insert into CountryMaster values('NSW','Australia')
insert into CountryMaster values('Melbourn','Australia')

insert into CountryMaster values('Islamabad','Pakistan')
insert into CountryMaster values('Karachi','Pakistan')
insert into CountryMaster values('Hyderabad','Pakistan')

select id, cityname, countryname,
ROW_NUMBER() OVER (PARTITION BY [countryName] ORDER BY id) AS Seq
from CountryMaster


Result
-------
.............................................
id cityname countryname Seq
.............................................
27 Canberra Australia 1
28 NSW Australia 2
29 Melbourn Australia 3

17 New Delhi India 1
18 Mumbai India 2
19 Kolkata India 3
20 Chennai India 4

30 Islamabad Pakistan 1
31 Karachi Pakistan 2
32 Hyderabad Pakistan 3

21 London UK 1
22 Amsterdam UK 2
23 Southampton UK 3

24 Washington US 1
25 New York US 2
26 Chicago US 3
.............................................

select id, cityname, countryname,
ROW_NUMBER() OVER (ORDER BY id) AS Seq
from CountryMaster

Result
-------
.............................................
id cityname countryname Seq
.............................................
17 New Delhi India 1
18 Mumbai India 2
19 Kolkata India 3
20 Chennai India 4
21 London UK 5
22 Amsterdam UK 6
23 Southampton UK 7
24 Washington US 8
25 New York US 9
26 Chicago US 10
27 Canberra Australia 11
28 NSW Australia 12
29 Melbourn Australia 13
30 Islamabad Pakistan 14
31 Karachi Pakistan 15
32 Hyderabad Pakistan 16
.............................................


select id, cityname, countryname,
ROW_NUMBER() OVER(order by id) AS Seq
from CountryMaster order by cityname