Use MySQL to select the next one record after a matching value without a sequential RowID

Question

How can I select all rows next to the row matching with the condition? But considering there is not a secuential RowID like ID Field

This is the table structure with 10 rows, but the number of rows will increase in the future.

Plain text:

ID      Date        N1
1     DateTime    9  
2     DateTime    2
3     DateTime    5
4     DateTime    9
5     DateTime    3
6     DateTime    6
7     DateTime    7
8     DateTime    9
20    DateTime    3 //Sequence broken
21    DateTime    9

Note: The "DateTime" string it's just symbolic, it would be any DateTime.

I am trying to know the first N1 number after the number: 9

I tried several ways but the result is not what I need.

The expected result is:

ID      Date        N1
2     DateTime      2
5     DateTime      3
20    DateTime      3

A simple way to achieve this was posted in this question, but only if the RowID keep the sequence.

Answer 1

You can create two Temporary Tables with one using the variable @row_num creating an index per row number and will increment each by one regardless of the RowID values. Then from this temp table you will select the row_num+1 from it where the N1 value equals 9. From there you select the id and N1 columns explicitly where row_num values match giving you the desired result.

SQL Query

SET @row_num:=0;
DROP TABLE IF EXISTS t1;
CREATE TEMPORARY TABLE t1 (SELECT @row_num:=@row_num+1 AS row_num, id, N1 FROM numbers);

DROP TABLE IF EXISTS t2;
CREATE TEMPORARY TABLE t2 (Select row_num+1 as row_num FROM t1 WHERE N1 = 9);

SELECT id, N1 FROM t1
WHERE row_num IN (Select * From t2);

---

Confirmation

Select * From Numbers (RowID equaling 5 is missing here)

Above SQL Query

---

Further Resources

• Temporary Table
• Create Table
• Drop Table
• MySQL Row_Number Emulation