I need some help with an SQL query.
Suppose I have a table A and B which look as follows.
TABLE_A
| UserID | Name |
|---|---|
| 123 | Max |
| 987 | Tom |
TABLE_B
| ExamID | UserID | Result | Date |
|---|---|---|---|
| 1 | 123 | 90 | 20.11.22 |
| 1 | 987 | 90 | 20.11.22 |
| 1 | 123 | 100 | 25.11.22 |
| 1 | 123 | 70 | 30.11.22 |
| 1 | 978 | 50 | 30.11.22 |
I would like to have an SQL statement, which returns either the highest Result or the newest Date for each user for a given exam.
So the result for highest score should look like this
| UserID | Name | ExamID | Result | Date |
|---|---|---|---|---|
| 123 | Max | 1 | 100 | 25.11.22 |
| 987 | Tom | 1 | 90 | 20.11.22 |
Or if I want to get the newest Date for each user of a given exam it would look like this
| UserID | Name | ExamID | Result | Date |
|---|---|---|---|---|
| 123 | Max | 1 | 10 | 30.11.22 |
| 987 | Tom | 1 | 50 | 30.11.22 |
The parameters would be @examID and @scorefilter (can be 'highest' or 'newest')
So far I had the following statement
SELECT * from TABLE_A a
join (
SELECT * FROM TABLE_B WHERE ExamID = @examID
ORDER BY CASE WHEN @scoreFilter = 'highest' THEN Result END DESC,
CASE WHEN @scpreFilter = 'newest' THEN Date END DESC
OFFSET 0 ROWS
) b on a.UserID = b.UserID
This just does return me all results for each user, but I only need to have the "First" for each of them.
I've looked at similar questions but there they used aggregate functions MAX on only one row. I need values of both rows and need to either have MAX(Result) or MAX(Date).
Is there any way to achieve this?
