Search code examples
sqlsql-serverrow-number

SQL Server Get one row for each student with highest date

I have two tables as follows: enter image description here

I want to find the StudentId, FirstName, StudentLoginInfoId, LoginDate. I am expecting only one entry per student with higher LoginDate.

Expected result:

enter image description here

Solution

  • You could use ROW_NUMBER to number output of the result-set for each partition (here each student) in a subquery and achieve your desired output by applying a condition of the number assigned for each student to be 1 which will equal one row.

    select studentid, firstname, studentlogininfoid, logindate
from (
  select 
    s.studentid, s.firstname, sl.studentlogininfoid, sl.logindate, 
    row_number() over (partition by sl.studentid order by sl.logindate desc) as rn
  from student s
  inner join studentlogininfoid sl on s.studentid = sl.studentid
) t
where rn = 1

    Explaining arguments for row_number:

    • PARTITION BY specifies what are your groups to enumerate separately (start from 1 for each group)
    • ORDER BY specifies how should rows be enumerated (based on which order)

    If we enumerate rows for each student and sort them from latest date descending, then the first row for each student (the row with rn = 1) will contain highest login date value for that student.