CODEWITHSUNDEEP
sqlsql-server
inincubus
inincubus

Reputation: 75

Find when the value changes in SQL Server

I want to return values when it changes from one to another.

In the below example, I want to return studentname,examnum, and grade when his/her grade goes down(from A to C) order by ExamNum(4 is the most recent exam). The rows to be returned are highlighted in Return column(the table doesn't have return column).

For example, I want to show Tom,4,B because he had A in examnum=3 but on his next exam he's go B (examnum=4).

I have no idea where to even begin..

SELECT Student,ExamNum,Grade, RowNum= ROW_NUMBER() OVER (PARTITION BY StudentORDER BY ExamNum) FROM dbo.Exam

    Student  ExamNum  Grade   Return
Tom              1      B
Tom              2      A
Tom              3      A
Tom              4      B       *
Angela           1      B
Angela           2      B
Angela           3      A
Angela           4      B       *
Shawn            1      A
Shawn            2      C       *
Shawn            3      A
Shawn            4      A

Upvotes: 1

Views: 1156

Answers (2)

Alexander Petrov
Alexander Petrov

Reputation: 9492

You can also call external code based on a trigger if you are interested to intercept the moment when the grade has actualy changed.

For oracle: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/jjdev/calling-Java-from-database-triggers.html#GUID-5C498DEF-0348-484D-AA26-2A88EF348D5C

For Ms SQL server : https://learn.microsoft.com/en-us/sql/advanced-analytics/java/howto-call-java-from-sql?view=sqlallproducts-allversions

You have two main aproaches. You can do Polling or you can Push the change.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use lag():

select e.*
from (select e.*,
             lag(e.grade) over (partition by e.student order by e.examNum) as prev_grade
      from exam e
     ) e
where e.grade < e.prev_grade;

Upvotes: 4

Related Questions