How can I Get The Oldest 3 Records for each student

  • i have result table which contains result for each student per month

    ID StudentID ResultDate Grade

    1 1 2011-01-01 00:00:00.000 A

    2 1 2011-03-01 00:00:00.000 D

    3 1 2011-05-01 00:00:00.000 A

    4 1 2011-08-01 00:00:00.000 B

    5 1 2011-12-01 00:00:00.000 C

    6 2 2011-02-01 00:00:00.000 D

    7 2 2011-03-01 00:00:00.000 D

    8 2 2011-06-01 00:00:00.000 B

    9 2 2011-08-01 00:00:00.000 C

    10 2 2011-10-01 00:00:00.000 D

    11 3 2011-01-01 00:00:00.000 A

    12 3 2011-05-01 00:00:00.000 B

    13 3 2011-08-01 00:00:00.000 A

    14 3 2011-09-01 00:00:00.000 B

    15 3 2011-11-01 00:00:00.000 B

    In My situation i shall get that data (The Oldest 3 Result Records for Each User)

    1 1 2011-01-01 00:00:00.000 A

    2 1 2011-03-01 00:00:00.000 D

    3 1 2011-05-01 00:00:00.000 A

    6 2 2011-02-01 00:00:00.000 D

    7 2 2011-03-01 00:00:00.000 D

    8 2 2011-06-01 00:00:00.000 B

    11 3 2011-01-01 00:00:00.000 A

    12 3 2011-05-01 00:00:00.000 B

    13 3 2011-08-01 00:00:00.000 A

    Note may be the table records will not be sorted by Result Date

    So how can i handle that SQL Statement 🙂 ???

  • Can you post the DDL (create table script) and hard coded inserts for us so we can be sure on the data and table structure. if you need help please see the link in my signature.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • Here is some code, but it is untested as you didn't provide all the required information to do any testing (no DDL, sample data in a readily consumable format).

    with cteStudentData as (

    select

    row_number() over (partition by StudentID order by ResultDate asc) as rownum,

    ID,

    StudentID,

    ResultDate,

    Grade

    from

    dbo.StudentDataTable)

    select

    ID,

    StudentID,

    ResultDate,

    Grade

    from

    cteStudentData

    where

    rownum <= 3

    order by

    StudentID,

    ResultDate;

  • well lynn you beat me to it. had almost the exact same code.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • Thanks a lot Lynn Pettisv 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply