January 10, 2022 at 7:30 pm
How to get first row group by partid and compliance typeid and document type?
I work on SQL server 2014 I face issue I can't get only one row
based on part id and compliance type id and document type .
so if have part id 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,
Row_Number int,
Regulation nvarchar(300),
Comp_Status nvarchar(100),
REVID int,
Doc_Type nvarchar(20),
Document_Type int,
ComplianceTypeID int
)
insert into FinalTableData
values
(35819351,1,'RoHS (2015/863)','Compliant with Exemption',340434330,'Contact',1362938,1),
(35819351,2,'RoHS (2015/863)','Compliant',288530768,'Web Page',1232162,1),
(35819351,3,'RoHS (2015/863)','NotCompliant',288539070,'Coc',1232160,1),
(35819351,1,'REACH 2021 (219)','Compliant',340434330,'Contact',1362938,2),
(35819351,1,'TSCA','Compliant',340434352,'CoC',1232160,11),
(35819351,2,'TSCA','Compliant',340434330,'Contact',1362938,11)
expected result
what I try
CREATE TABLE #TempTable
(
PartId int
)
insert into #TempTable(PartId)
select 35819351
SELECT
md.partid,
rohs.ComplianceTypeID as RohsCompliance,
reach.ComplianceTypeID as reachCompliance,
Rohs.Doc_Type AS Rohs_SourceType,
Reach.Comp_Status AS SVHCStatus,
case when Rohs.Regulation like '%2015%' then Rohs.Comp_Status else 'Unknown (Old Version Status)' end AS RohsRegulation,
case when Reach.Regulation like '%219%' then Reach.Comp_Status else 'Unknown (Old Version Status)' end AS ReachRegulation,
Reach.Doc_Type AS Reach_SourceType,
case when Reach.REVID is null then 9070 else Reach.REVID end AS Reach_Revision_ID,
case when TSKA.REVID is null then 7050 else TSKA.REVID end AS TSKA_Revision_ID,
TSKA.Comp_Status AS TSKAStatus ,
TSKA.Doc_Type AS TSKA_SourceType
FROM #TempTable MD
LEFT OUTER JOIN FinalTableData Rohs ON MD.PartID =Rohs.PartID AND Rohs.ComplianceTypeID=1 AND Rohs.Row_Number=1
LEFT OUTER JOIN FinalTableData Reach ON MD.PartID =Reach.PartID AND Reach.ComplianceTypeID=2 AND Reach.Row_Number=1
LEFT OUTER JOIN FinalTableData TSKA ON MD.PartID =TSKA.PartID AND TSKA.ComplianceTypeID=11 AND TSKA.Row_Number=1
EXPECTED result
partid RohsCompliance reachCompliance Rohs_SourceType SVHCStatus RohsRegulation ReachRegulation Reach_SourceType Reach_Revision_ID TSKA_Revision_ID TSKAStatus TSKA_SourceType
35819351 1 2 Web Page Compliant Compliant Compliant Contact 340434330 340434352 Compliant CoC
January 10, 2022 at 9:30 pm
I think part of the problem is how you are defining "first" and "group by". Since you have no ORDER BY in your query, you have no "first". If your data is unordered, how can you have a "first"?
What I would do first is add a column (via CTE or nested select) to your raw data to determine the order you want things to look up. Web Page, COC, and Contact should have an integer value assigned to them (I'd go with 1, 2, and 3, but you may want gaps in there to allow for more values to be added, so 10, 20, and 30 may make sense or 100, 200, and 300... use whatever makes sense to you). Now that you have that you have something you can order by, you can get the "First" value as it will be the MIN of that column.
If you did this with a CTE, I would then do a second CTE where you grab the MIN value of that calculated column partitioned by partId and Compliance Type ID. Now any row in this second CTE where the 2 calculated columns are equal is a row you want returned, so select only those rows.
I like using CTE's for problems like this as I can break it down into smaller steps and then later see which ones can be combined. Might not produce the most efficient solution, but it produces an easy to support solution and generally produces a "fast enough" solution.
I did not test this method, it is just the thought process I have with it - you need to order the values by an arbitrary method, so you need to define what that order is. Once you have that, the problem is a lot easier unless I am misunderstanding the problem.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply