Take the row after the specific row-CodePudding

I have the table, where I need to take the next row after the row which has course 'TA' and flag = 1. For this I created the column rnum (OVER DATE) which may help for finding it

| student | date  | course      | flag | rnum |
| ------- | ----- | ----------- | ---- | ---- |
| 1       | 17:00 | Math        | null | 1    |
| 1       | 17:10 | Python      | null | 2    |
| 1       | 17:15 | TA          | 1    | 3    |
| 1       | 17:20 | English     | null | 4    |
| 1       | 17:35 | Geography   | null | 5    |
| 2       | 16:10 | English     | null | 1    |
| 2       | 16:20 | TA          | 1    | 2    |
| 2       | 16:30 | SQL         | null | 3    |
| 2       | 16:40 | Python      | null | 4    |
| 3       | 19:05 | English     | null | 1    |
| 3       | 19:20 | Literachure | null | 2    |
| 3       | 19:30 | TA          | null | 3    |
| 3       | 19:40 | Python      | null | 4    |
| 3       | 19:50 | Python      | null | 5    |

As a result I should have:

| student | date  | course  | flag | rnum |
| ------- | ----- | ------- | ---- | ---- |
| 1       | 17:20 | English | null | 4    |
| 2       | 16:30 | SQL     | null | 3    |

CodePudding user response：

You can JOIN your table with itself on the next rnum and keep only the rows who match the right condition

SELECT T2.*
FROM MyTable T1
JOIN MyTable T2 ON T1.student = T2.student AND T2.rnum = T1.rnum   1 
WHERE T1.course = 'TA' AND T1.flag = 1

CodePudding user response：

Adding the rownumber was a good start, you can use it to join the table with itself:

WITH matches AS (
SELECT
  student, 
  rnum 
FROM table
WHERE flag = 1 
  AND course = 'TA' 
)
SELECT t.*
FROM table t
JOIN matches m
on t.student = m.student
and t.rnum = m.rnum   1