;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
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
2. Take DISTINCT of TestPRepetitionId Now the Row Count will be 100,886
3.. For this DISTINCT of TestPRepetitionId Take the Highest Version in Version Column
4. Based on DISTINCT of [TestPRepetitionId] and Highest Version in [Version ] Column get [ReportableResultId] & [AggregateResultId]
End of the Day Row Count will 100,886
-
Jingyang Li 5,891 Reputation points
2022-05-19T19:22:17.07+00:00
1 additional answer
Sort by: Most helpful
-
Olaf Helper 40,656 Reputation points
2022-05-20T06:18:36.237+00:00 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.