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

RKJohnny
February 14, 2023
126 views
0 votes
2 Answers

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;

Answers

- SelVazi
- February 14, 2023 at 1:37 pm
- 0 votes
0
You can do it using rank() to rank Marks then check if the rank and Sem are equal in every level.
```
WITH cte AS ( 
  SELECT Name, Sem, Marks,
  rank() OVER (PARTITION BY Name ORDER BY Marks) AS rank_mark 
  FROM Marks 
),
cte2 AS (
  SELECT *, if(Sem = rank_mark, 1, 0) as 'isUp'
  FROM cte
)
select t.*
from Marks t
left join (
  select Name
  from cte2 
  where isUp = 0
  group by Name
) as s on s.Name = t.Name 
WHERE s.Name is null;
```
Demo here
Login or Signup to reply.

- Stu
- February 14, 2023 at 1:42 pm
- 0 votes
0
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
Login or Signup to reply.