locked
error in query RRS feed

  • Question

  • User-309557751 posted

    hi all,

    SELECT s.OS_IdNo ,s.OS_SD_Name ,s.OS_SD_AdminNo , ms.MS_schoolnamesub , g.OS_GS_Standard , g.OS_GS_Division,OS_SD_AcadYear

    FROM OS_Studentdetails s, OP_ParentStudents p , OS_Masterinfosub ms , OS_Masterinfo m , OS_Generalsettings g

    where p.OP_OT_Id = @pid and OS_SD_TCStatus = 'N' and p.OS_IdNo = s.OS_IdNo and m.M_IdNo = s.M_IdNo and ms.MS_IdNo = s.MS_IdNo</div> <div>and s.OS_GS_IdNo = g.OS_GS_IdNo

    on  executing this query i am getting results as  same students with different academic year (OS_SD_AcadYear) is displaying here.  I want to avoid duplicate student names and   student with latest academic year to display. for eg ADHITHYA CHAKRAVARTHI  with academic year 2016-2017 only should come 

    4655  SIDHARTHA A NAIR                           11741            SARASWATHY VIDYALAYA    I A             2015-2016
    4945  GAUTHAM MUKUND GANESH       11874            SARASWATHY VIDYALAYA    V Z               2015-2016
    965   ADHITHYA CHAKRAVARTHI         1263             ARYA CENTRAL SCHOOL      IX B            2015-2016

    5449  NANDHANA R S NAIR                   12569           ARYA CENTRAL SCHOOL      VII E                2016-2017
    6993  ADHITHYAN R                                2428              ARYA KIDS                            UKG B             2015-2016

    8080   ADHITHYA CHAKRAVARTHI      1263              ARYA CENTRAL SCHOOL      X B            2016-2017

    could anyone please help me 

    Monday, April 18, 2016 2:58 PM

All replies

  • User941753370 posted

    Hi,

    Try:

    with CTE_RN as
    (
    SELECT s.OS_IdNo ,s.OS_SD_Name ,s.OS_SD_AdminNo , ms.MS_schoolnamesub , g.OS_GS_Standard , g.OS_GS_Division,OS_SD_AcadYear,
        ROW_NUMBER() OVER(PARTITION BY s.OS_SD_AdminNo ORDER BY OS_SD_AcadYear DESC) AS RN
    
    FROM OS_Studentdetails s, OP_ParentStudents p , OS_Masterinfosub ms , OS_Masterinfo m , OS_Generalsettings g
    
    where p.OP_OT_Id = @pid and OS_SD_TCStatus = 'N' and p.OS_IdNo = s.OS_IdNo and m.M_IdNo = s.M_IdNo and ms.MS_IdNo = s.MS_IdNo</div> <div>and s.OS_GS_IdNo = g.OS_GS_IdNo
    )
    
    select * from CTE_RN
    where RN = 1

    Hope this help

    Monday, April 18, 2016 6:48 PM