Stuck in SQL query

Hello Everyone, Let's start this series with a SQL Makeshifter (Jugaad). So, today I got stuck in a SQL query. Let's understand the problem statement and solution I did.

Problem Statement

I have a table where I have id (Primary Key), Date Entered, Name of person, Person Id, Score, Score Type

There can be multiple entries of same Person having same Score Type. We need single details of each person with each score type having maximum Date Entered.

Let's have a sample table

Table Name: Student
| id | Date Entered | Name of Person | Person Id | Score | Score Type |
-----------------------------------------------------------------------
| 1 | 2022-08-27 | Mark | 101 | 6 | AA |
| 2 | 2022-07-08 | Alex | 102 | 3 | AA |
| 3 | 2022-08-29 | Mark | 101 | 9 | AB |
| 4 | 2022-09-01 | Mark | 101 | 2 | AA |
| 5 | 2022-02-12 | Alex | 102 | 5 | AB |
| 6 | 2022-09-09 | Alex | 102 | 9 | AB |
----------------------------------------

Expected Output

| id | Date Entered | Name of Person | Person Id | Score | Score Type |
-----------------------------------------------------------------------
| 4 | 2022-09-01 | Mark | 101 | 2 | AA |
| 3 | 2022-08-29 | Mark | 101 | 9 | AB |
| 2 | 2022-07-08 | Alex | 102 | 3 | AA |
| 6 | 2022-09-09 | Alex | 102 | 9 | AB |
----------------------------------------

1st attempt to get this output

We have thought to use group by using having clause and we made this query.

SELECT * FROM student s GROUP BY Person Id, Score Type HAVING MAX(Date Entered);

We got wrong output using this query. We are not getting result having latest Date Entered.

2nd attempt to get this output

After spending time on google I have seen that everyone is using condition in HAVING clause, so I have tried this now

SELECT * FROM student s GROUP BY Person Id, Score Type HAVING Date Entered = MAX(Date Entered);

Again this didn't work

3rd attempt with successful result

After getting failed in 2nd attempt I tried it myself and tried to make a mapping in my mind and I have tried a sub query concept this time.

SELECT * FROM (SELECT * FROM student s GROUP BY Person Id, Score Type, Date Entered ORDER BY Date Entered desc) latest_student GROUP BY Person Id, Score Type;

Here I got my expected output

So this is what I have tried today with SQL. Now it's your turn to tell any other possible way to write a query having same expected result.

Do let me know your views on it in comment.

Comments

[Aaron Reese]

Firstly your table is not in 3rd Normal Form as name and person_id both refer to the person and not the score.
Nested SELECTs (called subqueries) work but are difficult to read and debug. I would have approached it by writing a CTE (common table expression) which is like a subquery but is given a named alias and written outside the main query: a CTE can also be referenced several times but that is not necessary in this example. Finally I would have used the RANK() OVER() function to give each score record a sequential ranking. The syntax is OVER (PARTITION BY {fields} ORDER BY {fields})
Partition tells you when to reset the counter and Order By tells you how to sort the records.

WITH CTE_RANK AS
(
SELECT
    Id, 
    RANK() OVER (PARTITION BY person_id, score_type ORDER BY date_entered DESC) AS score_rank
FROM 
    scores
)
SELECT 
           scores.*
FROM 
           scores
    JOIN 
           CTE_RANK on CTE_RANK.id = scores.id
WHERE
           CTE_RANK.score_rank = 1

[Lakshya Tyagi]

Yes, We can do that in above mysql 8.0 versions and I forgot to mention that we were using mysql 5.7 version. That's why I can't perform RANK() function in this query.

[Aaron Reese]

So you could achieve the same result by using MAX(date) and Group by person, score type in the CTR and then join to the CTE with person, score_type and date.

[Lakshya Tyagi]

Can you please write a query for the same. So that I can relate it.