CODEWITHSUNDEEP
sqlsql-serverjoin
Kimchi Man
Kimchi Man

Reputation: 1171

Get the latest data based on two columns in SQL

I have two tables,

Students
-------------
STUDENT_ID INT
CLASS_ID INT


Records
-----------
RECORD_ID INT
STUDENT_ID INT
CLASS_ID INT
TEST_SCORE INT
TEST_DATE DATE
TEST_SEQUENCE INT

And I want to get the result like the latest test score for each students in a specific class and a specific year.

For example, I want to get 2014's student test scores where CLASS_ID = 5. If there is more than two test scores for one student in the same date(TEST_DATE), I want to get the data which has highest TEST_SEQUENCE number.

I did it something like this:

SELECT s.* FROM STUDENTS s
INNER JOIN (SELECT STUDENT_ID, MAX(TEST_DATE) as Latest 
FROM Records GROUP BY STUDENT_ID) r
ON s.STUDENT_ID = r.STUDENT_ID AND s.TEST_DATE = r.Latest

So I can get the latest TEST_SCORE for each students.

But I don't know how to get the data if there are several records in the same TEST_DATE.

Upvotes: 0

Views: 65

Answers (1)

Lamak
Lamak

Reputation: 70648

;WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER( PARTITION BY STUDENT_ID
                                    ORDER BY TEST_DATE DESC, TEST_SEQUENCE DESC)
    FROM dbo.Records
    WHERE CLASS_ID = 5
    AND TEST_DATE >= '20140101'
)
SELECT S.*
FROM dbo.Students S
INNER JOIN CTE R
    ON S.STUDENT_ID = R.STUDENT_ID
WHERE R.RN = 1

Upvotes: 1

Related Questions