Question 401848 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – print only the student names who have performed better in their respective latest years

there is a table

student|Year|Marks
ABC|2023|98
XYZ|2021|100
MNO|2019|81
XYZ|2020|98
MNO|2018|98
ABC|2022|100

the result should just show

XYZ|2021|100

i am trying to create the query as

with cte as 
  (select *, row_number() over (partition by name, year order by marks) as rn
   from table1)

select name, year, marks
from CTE
where rn = 1

the condition which i am unable to include is the comparison of the marks in between the 2 years data.

Tags:

2

Answers


  1. 0

    One option could be using LAG() Over() analytic function to get last year’s marks in the same row allowing you to filter the data for any condition you need (marks better, worse, equal …)

    With     --  S a m p l e    D a t a:
  tbl ( student, a_year, marks ) AS
  ( Select 'ABC', 2023, 98 Union All
    Select 'XYZ', 2021, 100 Union All
    Select 'MNO', 2019, 81 Union All
    Select 'XYZ', 2020,  98 Union All
    Select 'MNO', 2018,  98 Union All
    Select 'ABC', 2022, 100
  )

           S Q L : 
SELECT t.*
FROM   ( Select student, a_year, marks, 
                LAG(marks) 
                    Over( Partition By student Order By a_year ) as last_year_marks
         From     tbl
       ) t
ORDER BY t.student, t.a_year Desc;

    /*      R e s u l t :
student a_year  marks   last_year_marks
------- ------  -----   ---------------
ABC       2023     98               100
ABC       2022    100              null
MNO       2019     81                98
MNO       2018     98              null
XYZ       2021    100                98
XYZ       2020     98              null    */

    … so, if you add Where clause to the outer query you could get the expected result …

           S Q L : 
SELECT t.*
FROM   ( Select student, a_year, marks, 
                LAG(marks) 
                    Over( Partition By student Order By a_year ) as last_year_marks
         From     tbl
       ) t
WHERE    t.marks > t.last_year_marks
ORDER BY t.student, t.a_year Desc;

    /*      R e s u l t :
student a_year  marks   last_year_marks
------- ------  -----   ---------------
XYZ       2021    100                98    */
    Login or Signup to reply.
  2. 0

    You can use NOT EXISTS to return each student’s last year result as long as it has the student’s highest marks.

    select t.*
from table1 t
where not exists (select * from table1 t2
                  where t2.student = t.student
                    and (t2.year > t.year or t2.marks > t.marks));

    MySQL users might want to do a self LEFT JOIN instead:

    select t.*
from table1 t
left join  table1 t2
  on  t2.student = t.student
  and (t2.year > t.year or t2.marks > t.marks)
where t2.student is null;

    MySQL version 8 users can use row_number() window function, partition by student, to make sure the latest year also has the highest marks:

    select student, year, marks
from
(
    select student, year, marks,
           row_number() over (partition by student order by marks desc) rn_m,
           row_number() over (partition by student order by year desc) rn_y
    from table1
) dt
where rn_m = 1 and rn_y = 1;

    Demo: https://dbfiddle.uk/vyNa1iq7 (based on @nbk’s fiddle, thanks!)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search