Click here to Skip to main content
15,742,400 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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)

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



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

PartId	FeatureName	FeatureValue	FeatureString	TechnologyId
7791	AC	5V	AC(5V)Boil(10v)Temp(52V)	4
7791	Boil	10v	AC(5V)Boil(10v)Temp(52V)	4
7791	Temp	52V	AC(5V)Boil(10v)Temp(52V)	4
8321	Angit	50V	Angit(50V)Fan(9v)Hot(3V),Wether(12V)	5
8321	Fan	9v	Angit(50V)Fan(9v)Hot(3V),Wether(12V)	5
8321	Hot	3V	Angit(50V)Fan(9v)Hot(3V),Wether(12V)	5
8321	Wether	12V	Angit(50V)Fan(9v)Hot(3V),Wether(12V)	5

What I have tried:

select *,DENSE_RANK() OVER (

ORDER BY FeatureString

) TechnologyId from #partsfeature
Updated 13-Jun-22 6:17am
Member 15627495 11-Jun-22 12:27pm    
you want to display the rows about "dense" before the 'select *' , and that in one query.

use UNION .
by UNION you can merge 2 queries to achieve the query you want.

SELECT fields... 
FROM table_ ...
SELECT fields..2  
FROM other table ...
// you 'll get :
row query 1
row query 1
row query 1
row query 2
row query 2
row query 2
row query 2
row query 2
row query 2
CHill60 13-Jun-22 12:08pm    
This makes no sense whatsoever, there is only one table in play so what is "other table" meant to be?

1 solution

Your Window clause is using the wrong column to get your desired results - you should be using PartID not FeatureString. You should also use a WHERE clause to ignore the TechnologyIds that are already set.
Try something like this
set TechnologyId= b.TechnologyId
from #partsfeature a
	SELECT PartID, DENSE_RANK() OVER (ORDER BY PartID) AS TechnologyId from #partsfeature
) b on a.PartId = b.PartId
where a.TechnologyId  is null
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900