Post reply

Select only rows with Max date column

  • March 1, 2017 at 12:14 pm

    #319158

    Please help..I need to create an sql to select all rows with maximum date in a column,
    My sample data:

    student Entry_dateSubjects
    a1/1/2016Math
    b1/12/2016Science
    c1/8/2016Chemistry
    b1/2/2016History
    c1/2/2016Algebra
    a1/5/2016Computer

    Desired Result:

    student Entry_dateSubjects
    a1/5/2016Computer
    b1/12/2016Science
    c1/8/2016Chemistry

    FYI i have hundred of rows..
    Thanks IN ADVANCE🙂

  • March 1, 2017 at 12:28 pm

    #1931045

    Sounds like homework and I don't know what version of MS SQL Server you are using so this may or may not work.

    with base as (
    select
      student,
      Entry_Date,
      Subjects,
      RN = row_number() over (partition by student order by Entry_Date desc)
    from
      dbo.YourTableName
    )
    select
      student,
      Entry_date,
      Subjects
    from
      base
    where
      RN = 1;

Viewing 2 posts - 1 through 2 (of 2 total)

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