How to make select Dense Rank over max of TechnologyId Based on FeatureString For new Inserted Data?

ahmed salah 3,216 Reputation points
2022-06-10T22:20:10.137+00:00

I work on sql server 2017 i have table have dense rank over FeatureString and column store rank increment is technology id
as below 

  create table #partsfeature  
 (  
 PartId int,  
 FeatureName varchar(300),  
 FeatureValue varchar(300),  
 FeatureString varchar(300),  
 TechnologyId int  
 )  
  insert into #partsfeature(PartId,FeatureName,FeatureValue,FeatureString,TechnologyId)  
  values  
  (1211,'AC','5V','AC(5V)Boil(10v)Temp(5V)',1),  
  (1211,'Boil','10v','AC(5V)Boil(10v)Temp(5V)',1),  
  (1211,'Temp','5V','AC(5V)Boil(10v)Temp(5V)',1),  
  (2421,'grail','51V','Alc(5V)Coil(9V)grail(51V)',2),  
  (2421,'Coil','9V','Alc(5V)Coil(9V)grail(51V)',2),  
  (2421,'Alc','5V','Alc(5V)Coil(9V)grail(51V)',2),  
  (6211,'compress','33v','compress(33v)heat(90v)push(80v)',3),  
  (6211,'heat','90v','compress(33v)heat(90v)push(80v)',3),  
  (6211,'push','80v','compress(33v)heat(90v)push(80v)',3)

Now max technology id on table part feature is 3

I need New Inserted data will be 4,5 for technology id

New Inserted Data as below 

  (7791,'AC','5V','AC(5V)Boil(10v)Temp(52V)'),  
  (7791,'Boil','10v','AC(5V)Boil(10v)Temp(52V)'),  
  (7791,'Temp','52V','AC(5V)Boil(10v)Temp(52V)'),  
  
  
  (8321,'Angit','50V','Angit(50V)Fan(9v)Hot(3V),Wether(12V)'),  
  (8321,'Fan','9v','Angit(50V)Fan(9v)Hot(3V),Wether(12V)'),  
  (8321,'Hot','3V','Angit(50V)Fan(9v)Hot(3V),Wether(12V)'),  
  (8321,'Wether','12V','Angit(50V)Fan(9v)Hot(3V),Wether(12V)')

so how to make select query over Feature String for column Technology Id as 4,5 for New inserted Data

expected result i need is

210404-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,714 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ahmed salah 3,216 Reputation points
    2022-06-11T08:44:30.317+00:00

    I solved my issue 

     DECLARE @MaxTechnologyID AS BIGINT =   
     
   ( SELECT MAX(TechnologyId)  
                                           FROM   #partsfeature  
                                         )  
  
        SELECT  *,CAST (DENSE_RANK() OVER ( ORDER BY FeatureString)  
                + @MaxTechnologyID AS BIGINT) AS NewTechnologyId   
 from #partsfeature