I am working on a report where I need to display a student id, the score and the lowest date/time that the score was filed. The table will have multiple scores per student across multiple days.
The problem I am having (using the sample code below) is that I am getting a new row for every score, instead of just one line per student showing the student id, score and date.
sample source table columns
student_id | score | score_date
select tests.student_id ,tests.score ,min(score_date) from tests group by tests.student_id ,tests.score
row_number() for this one.
select * from ( select t1.student_id ,t1.score ,row_number() over (partition by t1.student_id order by t1.score_date) rn ,t1.score_date from tests t1) t where t.rn = 1