Varshini S
asked on
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');
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Why use a varchar for SlNO? it will not sort correctly if you get to 10 or more. I suggest you use integer.