SQL display MIN() column with other columns displayed

2917 views sql
1

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

answered question

use row_number() window function to get the earliest date

2 Answers

9

You use 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

posted this
1

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?

JD

Please login first before posting an answer.