14,599,202 members
Rate this:
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

Rate this:

## Solution 3

I used this code to recreate your scenario (including it here in case you have a query on my solution!)
```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
```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 `UNPIVOT`s - 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.
```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
```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"
v2
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.
Rate this:

## Solution 1

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[^]
v2
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.