SQL Questions about getting MAX and MIN values based on two columns for the Ids from two others

Question

question

Mr.BobTopos asked Jun 7, '19

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!

sql tsql mssql columns max

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist commented · Jun 07, 2019 at 11:14 AM

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.

0 ·

Answer 1 · 2019-06-24T21:38:03Z

dbnerd3000 answered Jun 24, '19

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

capture.png (12.8 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question