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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3070

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3070
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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCommitted Points: 1686 More actions · Answer 1

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