CODEWITHSUNDEEP
sql-server
Hakka-4
Hakka-4

Reputation: 87

How to correctly PARTITION A TABLE?

task: I need to get the EARLIEST record row based on the Time_Frame column.

Problem: Many student have multiple records (student can enroll in different schools)

table format: Student_ID, Time_Frame plus random personal information columns.

Data Sample: Not sure how to load a .xls here but, I uploaded the data for just one student on the .pgn, I need a query that will return the yellow row, based on the time_frame column as that would be the earliest enrollment for this individual. I tried the code below but it does not work.

SELECT *
FROM (
    SELECT 
        t.*, 
       ROW_NUMBER() OVER(
            PARTITION BY  TIME_FRAME, STUDENT_ID
            ORDER BY STUDENT_ID DESC 
            ) rn
    from #test t
) t
WHERE rn = 1
and STUDENT_ID = '00000001A'

enter image description here

Upvotes: 0

Views: 49

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131706

If you want to find the earliest record by time_frame for a specific student only you need to order by time_frame :

SELECT *
FROM (
    SELECT 
        t.*, 
       ROW_NUMBER() OVER(ORDER BY TIME_FRAME ASC) rn
    from #test t
    WHERE STUDENT_ID = '00000001A' 
) t
WHERE rn = 1

If you want to find the earliest record for each student, you have to partition by Student_ID :

SELECT *
FROM (
    SELECT 
        t.*, 
       ROW_NUMBER() OVER(
            PARTITION BY  STUDENT_ID
            ORDER BY TIME_FRAME ASC 
            ) rn
    from #test t
) t
WHERE rn = 1

Upvotes: 2

Related Questions