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

Indudhar Gowda 426 Reputation points
2022-05-09T16:57:37.497+00:00

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

200327-image.png

  1. Need distinct of [TestPRepetitionId] & [ParameterCode] where [version] has Max value. to different table .(different Table Means : Test Table)
    200411-image.png 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.
Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,348 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,263 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jingyang Li 5,891 Reputation points
    2022-05-10T13:58:14.647+00:00
    /****** 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
    
    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. LiHong-MSFT 10,046 Reputation points
    2022-05-10T07:52:46.907+00:00

    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.

    1 person found this answer helpful.

  2. Jingyang Li 5,891 Reputation points
    2022-05-09T17:15:05.943+00:00
    /****** 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