using ROW_NUMBER()

Create table CityMaster (CityCode varchar(50),name varchar(5))

insert into CityMaster values ('1.1','AA');
insert into CityMaster values ('2.0','AB');
insert into CityMaster values ('3.3','AA' );
insert into CityMaster values ('9.9.0.1','CA');
insert into CityMaster values ('AA12345','CS');
insert into CityMaster values ('BA12345','CSS');
insert into CityMaster values ('FBA12345','ERD');
insert into CityMaster values ('SBA12345','SRD');
insert into CityMaster values ('CBA12345','DFG');

insert into CityMaster values ('1.1','AA1');
insert into CityMaster values ('2.0','AB1');
insert into CityMaster values ('3.3','AA1' );
insert into CityMaster values ('9.9.0.1','CA1');
insert into CityMaster values ('AA12345','CS1');
insert into CityMaster values ('BA12345','CSS1');
insert into CityMaster values ('FBA12345','ERD1');
insert into CityMaster values ('SBA12345','SRD1');
insert into CityMaster values ('CBA12345','DFG1');


insert into CityMaster values ('1.1','AAS');
insert into CityMaster values ('2.0','ABS');
insert into CityMaster values ('3.3','AAS' );
insert into CityMaster values ('9.9.0.1','CAS');
insert into CityMaster values ('AA12345','CSS');
insert into CityMaster values ('BA12345','CSSS');
insert into CityMaster values ('FBA12345','ERDS');
insert into CityMaster values ('SBA12345','SRDS');
insert into CityMaster values ('CBA12345','DFGS');

Open in new window

Script:

SELECT  CONVERT(VARCHAR, (ROW_NUMBER() OVER(ORDER BY CityCode DESC)))  AS Slno, CityCode,NAME FROM CityMaster

I am generating sequence number using ROW_NUMBER()  function. But I need the sequence number based on citycode
If city code start with numbers (0 to 9)  it should use separate sequence number .
If citycode has following value in first 2 characters  ('AA'  AND  'BA')  it should use separate sequence number
If citycode has following value in first 2 characters  ('FBA'  AND  'SBA' AND 'CBA' )  it should use separate sequence number

I need a  out put like this:

SlNO     CITYCODE         NAME
1              1.1                     AA
2              2.0                    AB
3              3.3                    AA
1         AA12345             CSS
2         BA12345             CSSS
1        FBA12345           ERDS
2        CBA12345           DFGS
Varshini SAsked:
Who is Participating?
 
John_VidmarCommented:
SELECT  Slno	=	CAST(ROW_NUMBER()
			OVER
			(	PARTITION BY
					CASE	WHEN LEFT(CityCode,2) IN ('AA','BA')		THEN RIGHT(RTRIM(CityCode),LEN(CityCode) - 2) + 'A'
						WHEN LEFT(CityCode,3) IN ('CBA','FBA','SBA')	THEN RIGHT(RTRIM(CityCode),LEN(CityCode) - 3) + 'B'
						ELSE CityCode
					END	
				ORDER BY CityCode DESC
			) as varchar)
,	CityCode
,	NAME
FROM	CityMaster

Open in new window

0
 
PortletPaulfreelancerCommented:
Why use a varchar for SlNO? it will not sort correctly if you get to 10 or more. I suggest you use integer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.