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
SELECT student_id, score, score_date FROM tests T INNER JOIN (SELECT student_id, Min(score_date) AS score_date FROM tests) X ON T.student_id = X.student_id AND T.score_date = X.score_date