SQL display MIN() column with other columns displayed

2917 views sql

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
from tests

group by tests.student_id

answered question

use row_number() window function to get the earliest date

2 Answers


You use row_number() for this one.

select * from (
    select t1.student_id
          ,row_number() over (partition by t1.student_id order by t1.score_date) rn 
    from tests t1) t
where t.rn = 1

posted this

Try this.

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

posted this

Have an answer?


Please login first before posting an answer.