Select single row from duplicate data

Question

I have a Postgresql data like:

Student Class Marks Observation tme
A        1     11    21/7/2020
A        2     13    18/7/2020   
B        1     19    17/7/2020
A        1     17    15/7/2020
B        1     15    21/7/2020

I want to obtain all the rows from the data such that if for two or more rows "student" and "class" is same it will give me row only with latest observation time.

Could someone please help me with the query? Thanks.

Answer 1

use row_number()

select * from (select *,row_number()over(partition by student,class order by observationtime desc) rn
from table_name
) a where a.rn=1

or distinct on

select distinct on (student,class) * from table_name
order by student,class , observationtime desc