Take the row after the specific row

user_17488892 0
Viewed 44

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    |
sql
2 Answers

There are many ways to get your desired result, let's see some of them.

1) EXISTS
You can use the EXISTS clause, specifying a subquery to match for the condition.

SELECT T2.*
FROM #MyTable T2
WHERE EXISTS (
    SELECT 'x' x
    FROM #MyTable T1
    WHERE T1.course = 'TA' AND T1.flag = 1 
    AND T1.student = T2.student AND T2.rnum = T1.rnum + 1
)

2) LAG
You ca use window function LAG to access previous row for a given order and then filter your resultset with your conditions.

SELECT w.student, w.date, w.course, w.flag, w.rnum
FROM (
    SELECT T1.*
        , LAG(course, 1) OVER (PARTITION BY student ORDER BY rnum) prevCourse
        , LAG(flag, 1) OVER (PARTITION BY student ORDER BY rnum) prevFlag
    FROM #MyTable T1
) w
WHERE prevCourse = 'TA' AND prevFlag = 1

3) JOIN
You can self-JOIN your table 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

4) CROSS APPLY
You can use CROSS APPLY to specify a subquery with the matching condition. It is pretty similar to EXISTS clause, but you will also get in your resultset the columns from the subquery.

SELECT T2.*
FROM #MyTable T2
CROSS APPLY (
    SELECT 'x' x
    FROM #MyTable T1
    WHERE T1.course = 'TA' AND T1.flag = 1 
    AND T1.student = T2.student AND T2.rnum = T1.rnum + 1
) x

5) CTE
You can use common table expression (CTE) to extract matching rows and then use it to filter your table with a JOIN.

;WITH
T1 AS (
    SELECT student, rnum
    FROM #MyTable T1
    WHERE T1.course = 'TA' AND T1.flag = 1
)
SELECT T2.*
FROM #MyTable T2
JOIN T1 ON T1.student = T2.student AND T2.rnum = T1.rnum + 1
user_45238960

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
user_155890100
Related