How to make select Dense Rank over max of TechnologyId Based on FeatureString Fo

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  June 11, 2022 at 6:40 am

  Go to Answer

  #4047877

  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

   

  

  what i try is

   

  select *,DENSE_RANK() OVER (

  ORDER BY FeatureString

  ) TechnologyId from #partsfeature

• Steve Collins

  SSCrazy

  Points: 2176

  More actions

  June 11, 2022 at 12:37 pm

  #4047977

  In which table is TechnologyId primary key?   Is there a linear dependency between PartId and TechnologyId?  Typically in general, if it's necessary to count (or rank) rows in order to insert into (what is ostensibly) a foreign key column, there's a serious omission/issue with the data model.   Ideally (maybe), 'FeaturesString' would would be a column (with UNIQUE constraint) in a table with TechnologyId as primary key.  Then you could use JOIN to determine the TechnologyId value(s).

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  June 11, 2022 at 5:39 pm

  Answer

  #4048058

  I solved 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

• June 27, 2022 at 7:26 am

  This was removed by the editor as SPAM

• July 9, 2022 at 6:29 am

  This was removed by the editor as SPAM

• July 13, 2022 at 9:09 am

  This was removed by the editor as SPAM