Query to find out latest data of each student

  • I have data as below

    Name--Month----Year--------Marks

    A--------8--------2012--------80

    A--------7--------2012--------88

    A--------3--------2011--------70

    B--------8--------2012--------80

    B--------7--------2012--------88

    B--------3--------2011--------70

    C--------3--------2012--------80

    C--------5--------2012--------88

    C--------7--------2012--------70

    I want result like this

    Name----Month----Year-------Marks

    A----------8--------2012--------80

    B----------8--------2012--------80

    C----------7--------2012--------70

    I want latest mark of each student (latest means by considering year,month). Is it possible in single sql query?

  • Is there any input parameter for Query?

  • Hi vyas,

    The query provided by you gives an error. you can run your query try with below table structure & data.

    create table tblStudent

    (

    Name varchar(100),

    Month int,

    Year int,

    Mark int

    )

    insert into tblStudent(Name,Month,Year,Mark) values ('A',8,2012,80)

    insert into tblStudent(Name,Month,Year,Mark) values ('A',7,2012,88)

    insert into tblStudent(Name,Month,Year,Mark) values ('A',3,2011,70)

    insert into tblStudent(Name,Month,Year,Mark) values ('B',8,2012,80)

    insert into tblStudent(Name,Month,Year,Mark) values ('B',7,2012,88)

    insert into tblStudent(Name,Month,Year,Mark) values ('B',3,2011,70)

    insert into tblStudent(Name,Month,Year,Mark) values ('C',3,2012,80)

    insert into tblStudent(Name,Month,Year,Mark) values ('C',5,2012,88)

    insert into tblStudent(Name,Month,Year,Mark) values ('C',7,2012,70)

  • Please

  • ;WITH CTE AS (

    SELECT

    Name,

    [Month],

    [Year],

    Mark,

    rn = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY <<Something>>)

    FROM tblStudent

    )

    SELECT

    Name,

    [Month],

    [Year],

    Mark

    FROM CTE

    WHERE rn = 1

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • In future will be careful not to tread on toes!

  • Jinxsee (9/27/2012)


    select

    VALUE, YEAR, MONTH, MARK from

    (select VALUE, YEAR, MONTH, MARK, ROW_NUMBER() OVER(PARTITION BY VALUE ORDER by YEAR desc, MONTH desc) as rownumber from TABLE

    group by VALUE,YEAR, MONTH, MARK) as a

    where a.rownumber = 1

    Excellent - but it's customary on ssc to encourage folks to put some effort into their homework. You may be sitting next to diptidjadhav in a couple of years' time, knowing that he used to post his homework on ssc then go to the pub whilst us folks here came up with all his answers!

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • My bad! Mental note added. ^^.

  • Jinxsee (9/27/2012)


    My bad! Mental note added. ^^.

    No worries - now how about fixing your code so that it runs against the OP's sample data? You don't need the GROUP BY.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hey thanks Jinxsee and ChrisM@Work. Your solution works perfect. Thanks a lot.

    One more question for Jinxsee, Suppose if we are using row_number() for a huge records it will minimize performance?

  • diptidjadhav (9/27/2012)


    Hey thanks Jinxsee and ChrisM@Work. Your solution works perfect. Thanks a lot.

    One more question for Jinxsee, Suppose if we are using row_number() for a huge records it will minimize performance?

    It's customary to post your chosen solution, diptidjadhav.

    As to whether or not ROW_NUMBER() affects performance, look at the execution plan of the query, then comment out the ROW_NUMBER() output column and look at it again. What do you reckon the answer might be? What operator exists in the plan when you use ROW_NUMBER()?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 11 posts - 1 through 10 (of 10 total)

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