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 TypeThere 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.
Oldest comments (4)
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.
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.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.
Can you please write a query for the same. So that I can relate it.