Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
<pre lang="text">
ResultID   Test_UK   Test_US   Test_UK_Score   Test_US_Score
   1	      1	        2	    48	            11
   2	      4	        1	    21	            24
   3	      3	        1	    55	            71
   4	      5	        6	    18	            78
   5	      7	        4	    19	            49
   6	      1	        3	    23	            69
   7	      5	        2	    98	            35
   8	      6	        7	    41	            47

The results I'm looking for:
TestID	HighestScore  LowestScore
   1	   71	          23
   2	   35	          11
   3	   69	          55
   4	   49	          21
   5	   98	          18
   6	   78	          41
   7	   47	          19


What I have tried:

I tried implementing a case of comparison, but I still ended up with subquery to pull out the final results. Also tried union, but it ends up in a sub query again. As far as I can think it should be a case when then query, but can't really come up with the logic for it, as it requires to match the ID's of the tests.
Thank you!
Posted
Updated 7-Jun-19 3:34am

I used this code to recreate your scenario (including it here in case you have a query on my solution!)
SQL
declare @tab table (ResultID  int, Test_UK   int, Test_US   int, Test_UK_Score   int, Test_US_Score int)
insert into @tab (ResultID,Test_UK,Test_US,Test_UK_Score,Test_US_Score) values
(   1	,      1	 ,       2	 ,   48	    ,        11),
(   2	,      4	 ,       1	 ,   21	    ,        24),
(   3	,      3	 ,       1	 ,   55	    ,        71),
(   4	,      5	 ,       6	 ,   18	    ,        78),
(   5	,      7	 ,       4	 ,   19	    ,        49),
(   6	,      1	 ,       3	 ,   23	    ,        69),
(   7	,      5	 ,       2	 ,   98	    ,        35),
(   8	,      6	 ,       7	 ,   41	    ,        47)

Now, I was able to get your results using a table variable (similar to my test data) but you would probably class that as a "temporary table". I was also able to get your results using a Common Table Expression (CTE) - but you would probably class that as a "sub-query" even though it isn't really.

There is another way you can do that using UNPIVOT[^]. The complication arises here because you need to UNPIVOT on two columns to get the results you need.
There is a worked example here - SQL Banana: SQL UNPIVOT on Multiple Columns[^]

I ended up with
SQL
select TestId, MAX(Score) AS MaxScore, MIN(Score) AS MinScore
FROM
(
	SELECT ResultId, Test_UK, Test_US, Test_UK_Score,Test_US_Score
	FROM @tab
) src
UNPIVOT
(
	TestId FOR T IN (Test_UK, Test_US)
) PVT1
UNPIVOT
(
	Score for S IN (Test_UK_Score, Test_US_Score)
) PVT2
WHERE SUBSTRING(S, 6,2) = SUBSTRING(T, 6,2)
GROUP BY TestID
Points to note:
- This may appear to look like a "sub-query" but that is just the syntax for an UNPIVOT query
- You don't have to "nest" the UNPIVOTs - you just chain them together
- Notice the "made up" columns T and S. This is where the real power of matching up Test_UK with Test_UK_Score happens - try taking out that WHERE clause and see what happens.

EDIT: Are you aware that your table design is not very good - it needs to be normalised. One way of doing that would be to use the UNION construct mentioned by @mohibur-rashid to insert normalised data into a new (permanent) table e.g.
SQL
declare @tab2 table (ResultID int, TestId int, Score int, TestCountry varchar(3))
insert into @tab2
select ResultId, Test_UK as TestId, Test_UK_Score as Score, 'UK' as TestCountry
from @tab
union
select ResultId, Test_US, Test_US_Score, 'US' as TestCountry
from @tab
Note the new column to indicate the country the test was taken in.
Why is this better? Well getting the results you wanted is trivial
SQL
select TestId, MAX(score) AS MaxScore, MIN(Score) as MinScore
from @tab2
GROUP BY TestId
It's also extensible. If you started taking the tests in Australia too then you would have to go to the trouble of changing your original table AND changing your query. But with the normalised version you just have to have a new code for TestCountry. e.g.
insert into @tab2 (ResultID, TestId, Score, TestCountry) values
(9, 8, 41, 'AU'),
(10, 8, 32, 'US'),
(11, 8, 42, 'UK')
And no changes to the query at all.

The UNPIVOT construct is often used to normalise tables "on the fly"
 
Share this answer
 
v2
Comments
Mohibur Rashid 7-Jun-19 10:00am    
You would use UNPIVOT but not SubQuery!
CHill60 7-Jun-19 10:18am    
It's the OP that has said they can't use Subquery (or temporary tables). I assume because their lecturer wants them to practice with UNPIVOT.
I don't class the inner queries on UNPIVOT, PIVOT, CTEs as "sub-queries" as it's actually part of the overall syntax.
I was just about to update my solution to point out that they have a very poor database design … unpivot is very often used to compensate for poor design.
Giving the answer would be easy.

But you need to learn few methods

1. SQL GROUP BY Statement[^]

2. SQL UNION, UNION ALL[^]

3. Finally SQL Derived Table[^]
 
Share this answer
 
v2
Comments
MrBobTopos 7-Jun-19 5:47am    
Yes, I looked into similar solutions, the problem is that all of it still comes up as a subquery, and I can't use them
Mohibur Rashid 7-Jun-19 6:47am    
Why is that?
CHill60 7-Jun-19 8:21am    
Probably because the tutor wants to push them towards using a different construct for their homework :-D
Mohibur Rashid 7-Jun-19 8:41am    
Do you think without subquery this can be achieved?
CHill60 7-Jun-19 9:22am    
Yes - I'm just preparing a solution. I'm going to give the OP the code which normally I wouldn't … but it's not obvious how to do it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900