SQL query to get the details of students who have continuously increasing marks in each semester from the Marks table

Question

I am having a table marks, I need to print the details of names having continuously increasing marks. Table : Marks

Name    Sem Marks
A       1   80
A       2   90
A       3   95
B       1   80
B       2   70
B       3   90
C       1   80
C       2   85
C       3   90

Output:

Name    Sem Marks
A       1   80
A       2   90
A       3   95
C       1   80
C       2   85
C       3   90

Can any one give me the MySQL query to print the output

Tried using analytical functions(lead, lag) but getting name with B also.

Current query

WITH cte AS ( SELECT Name, Sem, Marks, LAG(Marks,1) OVER (PARTITION BY Name ORDER BY Sem) AS Prev_Marks FROM Marks ) SELECT Name, Sem, Marks FROM cte WHERE Marks > Prev_Marks OR Prev_Marks IS NULL ORDER BY Name, Sem;

Answer 1

With a slight modification to your existing query you could use a correlation with not exists:

with d as (
  select Name,
  case 
    when Lag(Marks,1) over (partition by Name order by Sem) > Marks then 1
  end Decreasing
 from Marks
)
select Name, Sem, Marks 
from Marks m
where not exists (
  select * from d 
  where d.name = m.name and d.Decreasing = 1
)
order by name, sem;

Demo Fiddle