Solved

using ROW_NUMBER()

Posted on 2014-09-10
2
99 Views
Last Modified: 2014-09-12
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
0
Comment
Question by:Varshini S
2 Comments
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 40315787
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40315874
Why use a varchar for SlNO? it will not sort correctly if you get to 10 or more. I suggest you use integer.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql user function 7 31
SQL Query 2 43
SQL query bug 3 28
Linking a DMV to a database id/sql text in SQL server 2008 8 46
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now