Take average of only most recent group

There’s one table named StudentScore which has fields of: Score, CourseID, StudentID and Semester. The later three ones are the primary keys.

I want to write a stored procedure to get the average score of each student. But the rule is quite complex and I don’t know how to express it in one query. Nested query should be avoided if is possible.

  • login time out expired when setting sql server connection
  • PHP SQL Server Database Select
  • Sorting nvarchar column as integer
  • Character columns are silently truncated
  • How to iterate a string and add string to every position?
  • SQL Server execution plan Index seek
  • Here is the rule:

    If one student take a course for more than once, only the last score should be calculated.

    For example, there’re following data:

    StudentID    | CourseID  | Semester  | Score
    1              1           1           80
    1              2           1           40
    1              3           1           60
    1              2           2           50
    1              3           2           20
    2              1           1           90
    

    The stored procedure should return:

    StudentID    | AvgScore
    1              50 // which is avg(80, 50, 20)
    2              90
    

    Please suggest stored procedure as efficient as possible. Thanks!

  • Joining SQL Server UDF with params
  • T-SQL: ALTER VIEW error within a transaction and try-catch block
  • Most recent cost value as of date by item
  • SQL Azure : Connection to SQL Azure throws exception
  • How to use complex SQL in yii2
  • JOINING Same Table gives duplicate information
  • One Solution collect form web for “Take average of only most recent group”

    ;WITH x AS 
    (
      SELECT StudentID, Score, rn = ROW_NUMBER() OVER 
       (PARTITION BY StudentID, CourseID 
        ORDER BY Semester DESC) 
      FROM dbo.StudentScore
    )
    SELECT StudentID, AvgScore = AVG(Score)
    FROM x
    WHERE rn = 1
    GROUP BY StudentID;
    

    If you want something rounded to certain decimal places, maybe:

    ;WITH x AS 
    (
      SELECT StudentID, Score = 1.0*Score, rn = ROW_NUMBER() OVER 
       (PARTITION BY StudentID, CourseID 
        ORDER BY Semester DESC) 
      FROM dbo.StudentScore
    )
    SELECT StudentID, AvgScore = CONVERT(DECIMAL(10,2), AVG(Score))
    FROM x
    WHERE rn = 1
    GROUP BY StudentID;
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.