RANK () function in update sql

Question

I have 2 tables with incorrect Rank like below. is it possible to compare the data between these 2 tables and rank it based on the data available in the first table and rank the not available record in the first table in the value .

Table1:

PRODUCT1    LINE1      RANK1
12345       XXXX         1
12345       YYYY         2
12345       ZZZZ         3
34556       GGGG         1 

my table2 is currently having the below output

Table 2:

PRODUCT2 LINE2 SCORE RANK2 
12345    GGGG  100    1 
12345    JJKJ  90     2 
12345    WEJJ  80     3 
12345    CCCC  70     4 
12345    XXXX  60     5 
12345    YYYY  50     6 
12345    ZZZZ  40     7 
34556    AAAA  90     1 
34556    GGGG  80     2 
34556    HHHH  90     3 
34556    FFFF  80     4

I am expecting my table2 output as below after the update

PRODUCT2 LINE2 SCORE RANK2 
12345    XXXX   70    1 
12345    YYYY   50    2 
12345    ZZZZ   40    3 
12345    GGGG   100   4 
12345    JJKJ   90    5 
12345    WEJJ   80    6 
12345    CCCC   70    7 
34556    GGGG   80    1 
34556    HHHH   90    2 
34556    FFFF   80    3 
34556    AAAA   90    4

The query output is like below ,

PRODUCT2 LINE2 SCORE RANK2 
12345    XXXX   70    1 
12345    YYYY   50    2 
12345    ZZZZ   40    3 
12345    GGGG   100   4 
12345    JJKJ   90    5 
12345    WEJJ   80    6 
12345    CCCC   70    7 
34556    GGGG   80    1 
34556    HHHH   90    8 
34556    FFFF   80    9 
34556    AAAA   90    10

Due to the (SELECT MAX(RANK) FROM TEMP) in the answer it takes the max rank from the temp irrespective of the product number and assign the max number as rank for the remaining products. in this case 7 is the maximum rank number in product1 so it assigns the rank as 8,9,10 for the not available lines in table 2

Answer 1

Here you go

WITH TEMP AS (                                                    
    SELECT B.PRODUCT2,                                                
    B.LINE2, B.SCORE,                                                
    ROW_NUMBER() OVER () RANK                                        
    FROM TABLE1 A, TABLE2                                             
    B WHERE A.PRODUCT1 = B.PRODUCT2 AND A.LINE1 = B.LINE2             
)                                                                 
SELECT * FROM TEMP 
UNION                                          
SELECT PRODUCT2, LINE2, SCORE, 
    ROW_NUMBER() OVER () + (SELECT MAX(RANK) FROM TEMP) RANK           
FROM TABLE2 C                                                     
WHERE NOT EXISTS (                                                
    SELECT * FROM TABLE1 WHERE PRODUCT1 = C.PRODUCT2 AND LINE1 = C.LINE2
    ) 
ORDER BY RANK                                                     

Adding answer based on change in question

---

If you have different products, then while ranking them using rownumber, use Partition. This will rank "within" the partition, product in your case.

WITH TEMP AS (                                                   
    SELECT B.PRODUCT2,                                           
    B.LINE2, B.SCORE,                                            
    ROW_NUMBER() OVER (Partition by PRODUCT2) RANK               
    FROM TABLE1 A, TABLE2                                        
    B WHERE A.PRODUCT1 = B.PRODUCT2 AND A.LINE1 = B.LINE2        
)                                                                
SELECT * FROM TEMP                                               
UNION                                                            
SELECT PRODUCT2, LINE2, SCORE,                                   
    ROW_NUMBER() OVER (Partition by PRODUCT2)                    
+ (SELECT MAX(RANK) FROM TEMP where temp.product2 = C.product2) RANK                              
FROM TABLE2 C                                                    
WHERE NOT EXISTS (                                               
    SELECT * FROM TABLE1 WHERE PRODUCT1 = C.PRODUCT2 AND LINE1 = 
C.LINE2  )                                                       
ORDER BY product2 , RANK