Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Counter inside SELECT


Counter inside SELECT

Author
Message
RZ52
RZ52
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 74
Hi,

I have the following scenario :


DECLARE @TEST1 TABLE (C_NAME varchar(10), C_CODE VARCHAR(5))
INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('John', 'CL1')
INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('Jake', 'CL2')
INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('Joe', 'CL3')
INSERT INTO @TEST1(C_NAME,C_CODE) VALUES('Jane', 'CL4')


DECLARE @TEST2 TABLE (P_NAME varchar(10), P_CODE VARCHAR(5))
INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('Ray', 'PL91')
INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('James', 'PL92')
INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('Fred', 'PL93')
INSERT INTO @TEST2(P_NAME,P_CODE) VALUES('Mac', 'PL94')


DECLARE @x INT
SELECT @x = MAX(CONVERT(INT,SUBSTRING(C_CODE,3,2))) FROM @TEST1

INSERT INTO @TEST1(C_NAME, C_CODE)
(SELECT P_NAME,
   C_CODE = 'CL' + CONVERT(VARCHAR(5),@x+1)
   FROM @TEST2)



I need the new rows add to @Table1 but the C_CODE adds incrementally.

What I have in @Table1 after running the above code is :


C_NAME   C_CODE
----------------------
John    CL1
Jake    CL2
Joe    CL3
Jane    CL4
Ray    CL5
James CL5
Fred    CL5
Mac    CL5


But I need it to be :


C_NAME   C_CODE
----------------------
John    CL1
Jake    CL2
Joe    CL3
Jane    CL4
Ray    CL5
James CL6
Fred    CL7
Mac    CL8


Thanks in advance for helps.
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
This should work especially if you're not bothered about the order of the insert for Table 1



INSERT INTO @TEST1(C_NAME, C_CODE)
(SELECT P_NAME,
   C_CODE = 'CL' + CONVERT(VARCHAR(5),@x+ROW_NUMBER() OVER (ORDER BY PCODE))
   FROM @TEST2)




You might also want to consider changing the substring to be SUBSTRING(C_CODE,3,LEN(C_CODE)-2)) otherwise you'll have a problem when you get to 100, as you will pick up 10, so if you have 9 rows to insert the numbers will be in the range 11-20. so 100 will always be the max.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
RZ52
RZ52
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 74
Jason-299789 (3/7/2013)
This should work especially if you're not bothered about the order of the insert for Table 1



INSERT INTO @TEST1(C_NAME, C_CODE)
(SELECT P_NAME,
   C_CODE = 'CL' + CONVERT(VARCHAR(5),@x+ROW_NUMBER() OVER (ORDER BY PCODE))
   FROM @TEST2)




You might also want to consider changing the substring to be SUBSTRING(C_CODE,3,LEN(C_CODE)-2)) otherwise you'll have a problem when you get to 100, as you will pick up 10, so if you have 9 rows to insert the numbers will be in the range 11-20. so 100 will always be the max.


Dear Jason,

Thanks for guidance. However, in my real table the number coming with CL doesn't necessarily represent row number as some of rows are removed form table.
What I need to know is how I can make a counter inside a SELECT with a manually created seed.

Thanks again.
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3040 Visits: 5478
As you provided very limited details of your requirements I can suggest you reading this:
http://www.sqlservercentral.com/articles/T-SQL/68467/

This method may work for you.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19013
RZ52 (3/7/2013)
[quote]...What I need to know is how I can make a counter inside a SELECT with a manually created seed.

Thanks again.


That's exactly what Jason's code is designed to do. Did you run it and look at the results? In what way does it not fit your specification?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
RZ52
RZ52
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 74
RZ52 (3/7/2013)
Jason-299789 (3/7/2013)
This should work especially if you're not bothered about the order of the insert for Table 1



INSERT INTO @TEST1(C_NAME, C_CODE)
(SELECT P_NAME,
   C_CODE = 'CL' + CONVERT(VARCHAR(5),@x+ROW_NUMBER() OVER (ORDER BY PCODE))
   FROM @TEST2)




You might also want to consider changing the substring to be SUBSTRING(C_CODE,3,LEN(C_CODE)-2)) otherwise you'll have a problem when you get to 100, as you will pick up 10, so if you have 9 rows to insert the numbers will be in the range 11-20. so 100 will always be the max.


Dear Jason,

Thanks for guidance. However, in my real table the number coming with CL doesn't necessarily represent row number as some of rows are removed form table.
What I need to know is how I can make a counter inside a SELECT with a manually created seed.

Thanks again.


Dear Jason,

Your solution worked perfectly. That was my mistake to handle the OVER (ORDER BY) in wrong way.

Now it works.


Best Regards.
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
Thanks for the feedback, I was glad to help.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)

Group: General Forum Members
Points: 477 Visits: 1721
Just to add another option with a slightly more complex query that doesn't require a seed value. Also, by adding identity columns it will keep the rows in the same order.



IF OBJECT_ID('tempdb..#Test1') IS NOT NULL
DROP TABLE #Test1
IF OBJECT_ID('tempdb..#Test2') IS NOT NULL
DROP TABLE #Test2

CREATE TABLE #Test1 (ID INT IDENTITY(1,1),C_NAME VARCHAR(20),C_CODE VARCHAR(10),PRIMARY KEY(ID))
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('John','CL1')
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('Jake','CL2')
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('Joe','CL3')
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('Jane','CL4')

CREATE TABLE #Test2 (ID INT IDENTITY(1,1),P_NAME VARCHAR(20),P_CODE VARCHAR(10),PRIMARY KEY(ID))
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Ray', 'PL91')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('James', 'PL92')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Fred', 'PL93')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mac', 'PL94')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Martin', 'X911')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('George', 'X922')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Jon', 'ABCDE99993')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mary', 'BBB4')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Carl', 'DF691')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mackenzie', 'FGL9')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Sally', 'QW9883')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Karen', 'OPP')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Marty', 'ZZ11')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Abram', 'X122')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Zonnie', 'QWERTY9993')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Ryan', 'DCDCDC4')






;WITH cte AS
(
SELECT
R1.[C_NAME]
,'CL'+CAST(ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY R1.ID)+[MAX_C_CODE] AS VARCHAR(20)) AS [NEW_C_CODE]
FROM
(
SELECT DISTINCT
P1.ID
,P1.[P_NAME] AS [C_NAME]
,STUFF(MAX(C1.[C_CODE]) OVER (PARTITION BY 1),1,2,'') AS [MAX_C_CODE]
FROM
#Test1 C1
CROSS APPLY
#Test2 P1
WHERE
P1.ID > C1.ID
) R1
)
INSERT INTO #Test1
SELECT
C_NAME
,NEW_C_CODE
FROM
cte


SELECT * FROM #Test1



RZ52
RZ52
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 74
Steven Willis (3/7/2013)
Just to add another option with a slightly more complex query that doesn't require a seed value. Also, by adding identity columns it will keep the rows in the same order.



IF OBJECT_ID('tempdb..#Test1') IS NOT NULL
DROP TABLE #Test1
IF OBJECT_ID('tempdb..#Test2') IS NOT NULL
DROP TABLE #Test2

CREATE TABLE #Test1 (ID INT IDENTITY(1,1),C_NAME VARCHAR(20),C_CODE VARCHAR(10),PRIMARY KEY(ID))
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('John','CL1')
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('Jake','CL2')
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('Joe','CL3')
INSERT INTO #Test1 (C_NAME,C_CODE) VALUES ('Jane','CL4')

CREATE TABLE #Test2 (ID INT IDENTITY(1,1),P_NAME VARCHAR(20),P_CODE VARCHAR(10),PRIMARY KEY(ID))
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Ray', 'PL91')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('James', 'PL92')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Fred', 'PL93')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mac', 'PL94')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Martin', 'X911')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('George', 'X922')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Jon', 'ABCDE99993')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mary', 'BBB4')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Carl', 'DF691')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Mackenzie', 'FGL9')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Sally', 'QW9883')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Karen', 'OPP')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Marty', 'ZZ11')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Abram', 'X122')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Zonnie', 'QWERTY9993')
INSERT INTO #Test2(P_NAME,P_CODE) VALUES('Ryan', 'DCDCDC4')






;WITH cte AS
(
SELECT
R1.[C_NAME]
,'CL'+CAST(ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY R1.ID)+[MAX_C_CODE] AS VARCHAR(20)) AS [NEW_C_CODE]
FROM
(
SELECT DISTINCT
P1.ID
,P1.[P_NAME] AS [C_NAME]
,STUFF(MAX(C1.[C_CODE]) OVER (PARTITION BY 1),1,2,'') AS [MAX_C_CODE]
FROM
#Test1 C1
CROSS APPLY
#Test2 P1
WHERE
P1.ID > C1.ID
) R1
)
INSERT INTO #Test1
SELECT
C_NAME
,NEW_C_CODE
FROM
cte


SELECT * FROM #Test1







Dear Steven,

Thanks for suggestion. I think this would also help me in other situations as this has less limitation.

Regards.
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)

Group: General Forum Members
Points: 477 Visits: 1721
RZ52 (3/7/2013)
Thanks for suggestion. I think this would also help me in other situations as this has less limitation.

Another advantage of this method is that you can make an inline-table function (itvf) out of it if it's going to be something you need to run frequently.

Best of luck with your SQL projects!


 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search