ROW_NUMBER help 1

[sds814]

I have old product ids that I need to add as new product ids. All old product ids starting with 565 should increment starting from 123-00000. So 5650406316 should be 123-00000 and 5650406415 should be 123-00001. All old product ids starting with 566 should increment from 123-40000.

Here is my sample data:

CREATE TABLE PRODUCTS
(
BID int
,OLD_PROD_ID varchar(10)
)

INSERT INTO PRODUCTS(BID, OLD_PROD_ID)
SELECT '1035', '5650406316'
UNION
SELECT '1036', '5650406415'
UNION
SELECT '1015', '5660010217'     
UNION
SELECT '1021', '5660010217'

The data in the table should look like this:
BID OLD_PROD_ID
1015 5660010217
1021 5660010217
1035 5650406316
1036 5650406415
1015 123-40000
1021 123-40000
1035 123-00000
1036 123-00001

Here is the current code that I have:

IF OBJECT_ID('tempdb..#PRODUCTS') IS NULL
  BEGIN
        CREATE TABLE #PRODUCTS
        (
                BID int
                ,OLD_PROD_ID varchar(10)
                ,NEW_PROD_ID varchar(10)
        )

        INSERT INTO #PRODUCTS(BID, OLD_PROD_ID)
		SELECT BID
			,OLD_PROD_ID
		FROM Products   
  END
  
 
UPDATE #PRODUCTS
SET NEW_PROD_ID = NEW_ACCT_TABLE.NEW_PROD_ID
FROM #PRODUCTS Products
INNER JOIN
(SELECT DISTINCT OLD_PROD_ID
        ,CASE WHEN OLD_PROD_ID LIKE '565%' 
                        THEN '123-' + RIGHT(REPLICATE(0, 5) + CAST(ROW_NUMBER() OVER(ORDER BY OLD_PROD_ID DESC) - 1 AS VARCHAR(5)), 5)
                  ELSE '123-' + RIGHT(REPLICATE(0,5) + CAST(ROW_NUMBER() OVER(ORDER BY OLD_PROD_ID DESC) + 39999 AS VARCHAR(5)), 5) 
                  END AS NEW_PROD_ID
FROM #PRODUCTS) NEW_ACCT_TABLE
        ON Products.OLD_PROD_ID = NEW_ACCT_TABLE.OLD_PROD_ID
        
INSERT INTO PRODUCTS(BID , OLD_PROD_ID)
SELECT BID 
	,NEW_PROD_ID
FROM #PRODUCTS
WHERE NEW_PROD_ID IS NOT NULL

When I return the result set of Products with the query above I get:
BID OLD_PROD_ID
1015 5660010217
1021 5660010217
1035 5650406316
1036 5650406415
1015 123-40000
1021 123-40001
1035 123-00003
1036 123-00002

There are two problems with the code I have:
1) BIDs 1015 and 1021 should be sharing the same NEW_PROD_ID: 123-40000. Is that because the ROW_Number that I'm doing on OLD_PROD_ID is not distinct? And if so, do I first need to get the distinct OLD_PROD_IDs?

2) 1035 should have OLD_PROD_ID as 123-00000 and 1036 should have OLD_PROD_ID as 123-00001. I could have two update statements and have a where condition in each of the update statements to get 565 and then 566. So the sql would be something like:

UPDATE #PRODUCTS
SET NEW_PROD_ID = NEW_ACCT_TABLE.NEW_PROD_ID
FROM #PRODUCTS Products
INNER JOIN
(SELECT DISTINCT OLD_PROD_ID
        ,'123-' + RIGHT(REPLICATE(0, 5) + CAST(ROW_NUMBER() OVER(ORDER BY OLD_PROD_ID ASC) - 1 AS VARCHAR(5)), 5) AS NEW_PROD_ID
FROM #PRODUCTS
WHERE OLD_PROD_ID LIKE '565%') NEW_ACCT_TABLE
        ON Products.OLD_PROD_ID = NEW_ACCT_TABLE.OLD_PROD_ID

And have a similar update statement for 566. However, I would rather have it one update statement.

Thanks for the help!

 

[bborissov]

DECLARE @PRODUCTS TABLE (BID int, OLD_PROD_ID varchar(10)) 


INSERT INTO @PRODUCTS (BID, OLD_PROD_ID)
SELECT '1035', '5650406316'
UNION
SELECT '1036', '5650406415'
UNION
SELECT '1015', '5660010217'
UNION
SELECT '1021', '5660010217'


SELECT Bid
     , OLD_PROD_ID
     , '123-' + CASE WHEN LEFT(OLD_PROD_ID,3) = '565'
                          THEN RIGHT('00000'+CAST(Rnk-1 as varchar(5)),5)
                     WHEN LEFT(OLD_PROD_ID,3) = '566'
                          THEN RIGHT('00000'+CAST(Rnk+3999 as varchar(5)),5)
                 ELSE '00000' END
FROM (
SELECT *, RANK() OVER(PARTITION BY LEFT(OLD_PROD_ID,3) ORDER BY OLD_PROD_ID DESC) AS Rnk
FROM @PRODUCTS) Tst

Borislav Borissov
VFP9 SP2, SQL Server

 

[sds814]

Thanks Boris!

I read that RANK() skips a number if the previous two numbers have an identical ranking. So in the case if I had another OLD_PROD_ID that started with 566 the new product id would be skipping a product id.

DECLARE @PRODUCTS TABLE (BID int, OLD_PROD_ID varchar(10)) 


INSERT INTO @PRODUCTS (BID, OLD_PROD_ID)
SELECT '1035', '5650406316'
UNION
SELECT '1036', '5650406415'
UNION
SELECT '1015', '5660010217'
UNION
SELECT '1021', '5660010217'
UNION
SELECT '1092', '5660010290'


SELECT Bid
     , OLD_PROD_ID
     , '123-' + CASE WHEN LEFT(OLD_PROD_ID,3) = '565'
                          THEN RIGHT('00000'+CAST(Rnk-1 as varchar(5)),5)
                     WHEN LEFT(OLD_PROD_ID,3) = '566'
                          THEN RIGHT('00000'+CAST(Rnk+3999 as varchar(5)),5)
                 ELSE '00000' END
FROM (
SELECT *, RANK() OVER(PARTITION BY LEFT(OLD_PROD_ID,3) ORDER BY OLD_PROD_ID ASC) AS Rnk
FROM @PRODUCTS) Tst

What would I have to do if I wanted 5660010290 to have the new product id as 123-04001?

 

[bborissov]

Then use DENSE_RANK ( ) instead of RANK():

DECLARE @PRODUCTS TABLE (BID int, OLD_PROD_ID varchar(10)) 


INSERT INTO @PRODUCTS (BID, OLD_PROD_ID)
SELECT '1035', '5650406316'
UNION
SELECT '1036', '5650406415'
UNION
SELECT '1015', '5660010217'
UNION
SELECT '1021', '5660010217'
UNION
SELECT '1092', '5660010290'



SELECT Bid
     , OLD_PROD_ID
     , '123-' + CASE WHEN LEFT(OLD_PROD_ID,3) = '565'
                          THEN RIGHT('00000'+CAST(Rnk-1 as varchar(5)),5)
                     WHEN LEFT(OLD_PROD_ID,3) = '566'
                          THEN RIGHT('00000'+CAST(Rnk+3999 as varchar(5)),5)
                 ELSE '00000' END
FROM (
SELECT *,   DENSE_RANK ( ) OVER(PARTITION BY LEFT(OLD_PROD_ID,3) ORDER BY OLD_PROD_ID) AS Rnk
FROM @PRODUCTS) Tst

Borislav Borissov
VFP9 SP2, SQL Server

 

[sds814]

That works perfectly. Why did SQL Server come up with Rank() then? If the functionality of Rank() was required couldn't that be achieved by using Dense_Rank()?

 

[bborissov]

Hmmm,
that is question for MS

Borislav Borissov
VFP9 SP2, SQL Server