SQL Questions about getting MAX and MIN values based on two columns for the Ids from two others
Hello guys, I'm having difficulties figuring a query out, would someone be able to assist me with this?
Problem: 4 columns that represent results for the 2 separate tests. One of them taken in UK and another in US. Both of them are the same test and I need to find the highest and lowest score for the test taken in both countries. I also need to avoid using subqueries and temporary tables. Would appreciate theoretical ideas and actual solutions for the problem.
The table looks similar to this:
The results I'm looking for:
Thank you!
The sections "The table looks similar to this" and "The results I'm looking for" are empty, and I can't really work it out from just the text.
Agree with @Magnus Ahlkvist. its difficult without those sections but here is a punt on what you may need.
I really like using partitions, its so much easier than grouping by..
SELECT DISTINCT [Testname]
,[Country]
,MAX([Score]) OVER (PARTITION BY Testname,Country) as MaxScore
,MIN([Score]) OVER (PARTITION BY Testname,Country) as MinScore
FROM [AdventureWorks2012].[dbo].[TestTable]
Have attached an image of the table and results from this query
