Click here to Skip to main content
15,393,637 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)
   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

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
Posted
Updated 13-Jun-22 6:17am
Comments
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_ ...
UNION
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
SQL
UPDATE a
set TechnologyId= b.TechnologyId
from #partsfeature a
INNER JOIN 
(
	SELECT PartID, DENSE_RANK() OVER (ORDER BY PartID) AS TechnologyId from #partsfeature
) b on a.PartId = b.PartId
where a.TechnologyId  is null
   

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