question

IDGO-6443 avatar image
0 Votes"
IDGO-6443 asked IDGO-6443 commented

Need SQL Query to Get distinct of Column with max of that with Other Columns

I have Provided the DB Backup and Screenshots to make life easier.

DB backup : https://1drv.ms/u/s!Ag8QU6ar3yRugYxQbVegOrsJImt3fg?e=O9Ud3d

Steps required in Order with Screenshot

1. Total Row Count of ReportableResults is 309,290
203814-1.png


2. Take DISTINCT of TestPRepetitionId Now the Row Count will be 100,886
203815-2.png


3.. For this DISTINCT of TestPRepetitionId Take the Highest Version in Version Column
203795-3.png


4. Based on DISTINCT of [TestPRepetitionId] and Highest Version in [Version ] Column get [ReportableResultId] & [AggregateResultId]
203766-4.png

End of the Day Row Count will 100,886










sql-server-generalsql-server-transact-sqlazure-sql-databasesql-server-reporting-servicessql-server-integration-services
1.png (114.0 KiB)
2.png (130.1 KiB)
3.png (130.5 KiB)
4.png (133.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @IDGO-6443
Please check the first two rows in your image. They have the same TestPRepetitionId and same Version. So,in this situation, how to define the highest Version? Don't we need a tiebreaker?
203931-image.png
Best regards,
LiHong

0 Votes 0 ·
image.png (30.6 KiB)
JingyangLi avatar image
1 Vote"
JingyangLi answered IDGO-6443 commented
 ;with mycte as (SELECT  [ReportableResultId]
       ,[ReportableResultCode]
       ,[ParameterCode]
       ,[IsAggregated]
       ,[TestPRepetitionId]
       ,[AggregateResultId]
       ,[Version]
       ,row_number() Over(partition by TestPRepetitionId order by  Version desc) rn
   FROM [Test].[dbo].[ReportableResults]
   )
    
   select 
    [ReportableResultId]
       ,[ReportableResultCode]
       ,[ParameterCode]
       ,[IsAggregated]
       ,[TestPRepetitionId]
       ,[AggregateResultId]
       ,[Version]
       from mycte
   where rn=1
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @JingyangLi , It worked as expected.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered IDGO-6443 commented

Screenshots to make life easier.

It don't, because I can't query screenshots.
Please post table design as DDL, some sample data as DML statement and the expected result.
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I have given the DB Backup...

0 Votes 0 ·