Need SQL Query for distinct of two columns which has max value in 3rd Column

Question

Need SQL Query for distinct of two columns which has max value in 3rd Column Requirement : See Bullet Point 1,2,3 & 4.

I have provided the DB backup to make life easy.
https://1drv.ms/u/s!Ag8QU6ar3yRugYxL29T3C-ORyj5bHg?e=AH4VpF

1. Need distinct of [TestPRepetitionId] & [ParameterCode] where [version] has Max value. to different table .(different Table Means : Test Table)
   here is the Example
2. Copy [AggregateResultId] not null to different Table** (different Table Means : Test Table)
3. [ReportableResultId] & [TestPRepetitionId] both column should be unique, If Duplicate Exists need to removed in Test Table. (Meaning [ReportableResultId] & [TestPRepetitionId] should not repeated.
4. Merge All Query to 1.

Answer 1

/****** Script for SelectTopNRows command from SSMS  ******/
 ;with mycte as (
 SELECT   [ReportableResultId]
       ,[ReportableResultCode]
       ,[ParameterCode]
       ,[IsAggregated]
       ,[TestPRepetitionId]
       ,[AggregateResultId]
       ,[Version]
       ,row_number() Over(partition by  [ParameterCode],[TestPRepetitionId] order by [version]  desc ) rn
   FROM [Test].[dbo].[Newtable]
   where  IsAggregated = 0 and AggregateResultId is not null

   )

,mycte1 as (
SELECT   [ReportableResultId]
       ,[ReportableResultCode]
       ,[ParameterCode]
       ,[IsAggregated]
       ,[TestPRepetitionId]
       ,[AggregateResultId]
       ,[Version]
       ,row_number() Over(partition by  [ParameterCode],[TestPRepetitionId] order by [version]  desc ) rn
   FROM [Test].[dbo].[Newtable]
   where IsAggregated = 1

   )


   ,mycte2 as (
   Select [ReportableResultId]
       ,[ReportableResultCode]
       ,[ParameterCode]
       ,[IsAggregated]
       ,[TestPRepetitionId]
       ,[AggregateResultId]
       ,[Version] 
  from mycte    where rn=1
   UNION ALL 
Select [ReportableResultId]
       ,[ReportableResultCode]
       ,[ParameterCode]
       ,[IsAggregated]
       ,[TestPRepetitionId]
       ,[AggregateResultId]
       ,[Version] 
  from mycte1
  )

Select * 
into TestTable  --newtable
from mycte2 



   select * from TestTable  --8829 rows

   drop table if exists TestTable

Answer 2

Hi @Indudhar Gowda
Please check this:

;WITH CTE1 AS   
(  
 SELECT [ReportableResultId],[ReportableResultCode],[ParameterCode],[IsAggregated],[TestPRepetitionId],[AggregateResultId],[Version]  
       ,ROW_NUMBER()OVER(PARTITION BY [ParameterCode],[TestPRepetitionId] ORDER BY [Version] DESC) RNum  
 FROM [Test].[dbo].[Newtable]  
),CTE2 AS  
(  
 SELECT [ReportableResultId],[ReportableResultCode],[ParameterCode],[IsAggregated],[TestPRepetitionId],[AggregateResultId],[Version]  
       ,ROW_NUMBER()OVER(PARTITION BY [ReportableResultId] ORDER BY [ReportableResultCode]) RNum_ReportableResultId  
	   ,ROW_NUMBER()OVER(PARTITION BY [TestPRepetitionId] ORDER BY [ReportableResultCode]) RNum_TestPRepetitionId  
 FROM CTE1  
 WHERE RNum=1  
)  
SELECT [ReportableResultId],[ReportableResultCode],[ParameterCode],[IsAggregated],[TestPRepetitionId],[AggregateResultId],[Version]  
INTO #TestTable  
FROM CTE2   
WHERE RNum_ReportableResultId=1 AND RNum_TestPRepetitionId=1   
      
SELECT * FROM #TestTable    

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

/****** Script for SelectTopNRows command from SSMS  ******/
;with mycte as (SELECT   [ReportableResultId]
      ,[ReportableResultCode]
      ,[ParameterCode]
      ,[IsAggregated]
      ,[TestPRepetitionId]
      ,[AggregateResultId]
      ,[Version]
      ,row_number() Over(partition by  [ParameterCode],[TestPRepetitionId] order by [version]  desc ) rn
  FROM [Test].[dbo].[Newtable]
  where AggregateResultId is not null

  )

  Select [ReportableResultId]
      ,[ReportableResultCode]
      ,[ParameterCode]
      ,[IsAggregated]
      ,[TestPRepetitionId]
      ,[AggregateResultId]
      ,[Version]

      INTO  TestTable  --newtable


 from mycte 
  where rn=1

  select * from TestTable  --4592 rows