Post
Ask Question
ahmed elbarbary

ahmed elbarbary

  • 642
  • 1.6k
  • 282.8k

How to get first row group by partid and compliance typeid and documen

Jan 10 2022 6:48 PM

I work on sql server 2014 I face issue I can't get only one row based on partid and compliance type id
and document type

so if have partid as 3581935 and compliance type id 1 and document type

Web Page OR Coc OR Contact

then first priority will be Web Page

second priority Coc

third priority Contact

so my sample as below :

create table FinalTableData(PartId int,Regulation nvarchar(300),
Comp_Status nvarchar(100),REVID    int,Doc_Type nvarchar(20),
Document_Type int,ComplianceTypeID int)
insert into FinalTableData values(35819351,'RoHS (2015/863)','Compliant with Exemption',340434330,'Contact',1362938,1),
(35819351,'RoHS (2015/863)','Compliant',288530768,'Web Page',1232162,1),
(35819351,'RoHS (2015/863)','NotCompliant',288539070,'Coc',1232160,1),
(35819351,'REACH 2021 (219)','Compliant',340434330,'Contact',1362938,2),
(35819351,'TSCA','Compliant',340434352,'CoC',1232160,11),
(35819351,'TSCA','Compliant',340434330,'Contact',1362938,11)

my expected result :

PartId Regulation Comp_Status REVID Doc_Type Document_Type ComplianceTypeID
35819351 RoHS (2015/863) Compliant 288530768 Web Page 1232162 1
35819351 REACH 2021 (219) Compliant 340434330 Contact 1362938 2
35819351 TSCA Compliant 340434352 CoC 1232160 11

Answers (1)