question

Mr.BobTopos avatar image
Mr.BobTopos asked

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!

sqltsqlmssqlcolumnsmax
1 comment
10 |1200

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

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 Likes 0 ·

1 Answer

·
dbnerd3000 avatar image
dbnerd3000 answered

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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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