SQL Get Only Most Recent Record for A User

Question

I have a situation where I need to write a sql query to get all of the most recent responses for a student in a classroom. I basically want to show just their most recent response, not all of their responses. I have the query to get all of the responses and order them, however I can't figure out the part where it only grabs that user's most recent record.

Below is the query I have to this point. You can see from the sample data in the image it is pulling back all responses. What I basically want is either just the most recent for a particular student OR possibly just showing the max attempt for a particular Lesson/Page number combo. I have tried playing around with partition and group bys but I haven't found the right combination yet.

SELECT U.UserName, C.Name AS 'ClassroomName', U.FirstName, U.LastName, L.Name AS 'LessonName', P.PageNumber, R.Attempt, R.Created
FROM Responses R
INNER JOIN ClassroomUsers CU ON CU.UserId = R.UserId
INNER JOIN Classrooms C ON C.Id = CU.ClassroomId
INNER JOIN Questions Q ON Q.Id = R.QuestionId
INNER JOIN Pages P ON P.Id = Q.PageId
INNER JOIN Lessons L ON L.Id = P.LessonId
INNER JOIN AspNetUsers U ON U.Id = CU.UserId
WHERE CU.ClassroomId IN (
    SELECT CU.ClassroomId
    FROM ClassroomUsers CU
    WHERE CU.UserId = @UserId
)
ORDER BY R.Created DESC

See https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group — PM 77-1, Jun 12 '18 at 17:41

score 1 · Accepted Answer · answered Jun 12 '18 at 17:38

My favorite way of doing this is using Row_Number() which will number each row based upon the criteria you set - In your case, you'd partition by U.UserName since you want one row returned for each user and order by R.Created DESC to get the latest one.

That being the case, you'd only want to get back the rows that have RN=1, so you query that out as follows:

WITH cte AS
(
    SELECT
     ROW_NUMBER() OVER(PARTITION BY U.UserName ORDER BY R.Created DESC) AS RN
     ,U.UserName, C.Name AS 'ClassroomName', U.FirstName, U.LastName, L.Name AS 'LessonName', P.PageNumber, R.Attempt, R.Created
    FROM Responses R
    INNER JOIN ClassroomUsers CU ON CU.UserId = R.UserId
    INNER JOIN Classrooms C ON C.Id = CU.ClassroomId
    INNER JOIN Questions Q ON Q.Id = R.QuestionId
    INNER JOIN Pages P ON P.Id = Q.PageId
    INNER JOIN Lessons L ON L.Id = P.LessonId
    INNER JOIN AspNetUsers U ON U.Id = CU.UserId
    WHERE CU.ClassroomId IN (
        SELECT CU.ClassroomId
        FROM ClassroomUsers CU
        WHERE CU.UserId = @UserId
    )
)


SELECT * FROM cte WHERE RN = 1

Hope that makes sense / helps!!

Thanks for the response. This seems to do the trick. – DB at PS Jun 12 '18 at 17:54 — DB at PS, Jun 12 '18 at 17:54

score 1 · Answer 2 · answered Jun 12 '18 at 17:47

Just another option is using the WITH TIES clause.

Example

SELECT top 1 with ties
    U.UserName
  , C.Name AS 'ClassroomName'
  , U.FirstName
  , U.LastName
  , L.Name AS 'LessonName'
  , P.PageNumber
  , R.Attempt
  , R.Created
FROM Responses R
INNER JOIN ClassroomUsers CU ON CU.UserId = R.UserId
INNER JOIN Classrooms C ON C.Id = CU.ClassroomId
INNER JOIN Questions Q ON Q.Id = R.QuestionId
INNER JOIN Pages P ON P.Id = Q.PageId
INNER JOIN Lessons L ON L.Id = P.LessonId
INNER JOIN AspNetUsers U ON U.Id = CU.UserId
WHERE CU.ClassroomId IN (
      SELECT CU.ClassroomId
        FROM ClassroomUsers CU
        WHERE CU.UserId = @UserId
      )
Order By ROW_NUMBER() OVER(PARTITION BY U.UserName ORDER BY R.Created DESC)

Thanks for the response. This query also seems to pull back the information I was looking for. — DB at PS, Jun 12 '18 at 17:54

SQL Get Only Most Recent Record for A User

2 Answers2