How do I return only the most recent record on a date field split into two

Question

Scenario: Person A takes test B three times in the span of two year. There will be three entries for that person. However, I need to write a query that tells me the number of Persons that have taken a test(just one, the latest test). The problem with this is I have a column labeled, Test_Month (xx) and Test_year(xx).

What I need: I need to be able to just pull the test with the most recent test month and year, basically the most recent test they took. (For example(see pic below) I need, the record for 2/20 only.)

I have no idea how to retrieve only one record per person by the last test they took based on the separate columns test_Month and test_year.

Answer 1

You can use window functions:

select *
from (
    select 
        t.*, 
        row_number() over(
            partition last_name, firt_name 
            order by test_year desc, test_month desc
        ) rn
    from mytable t
) t
where rn = 1