SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,318 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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