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

Question

How can I select all rows next to the row matching with the condition?

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

Table structure 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
9     DateTime    3
10    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
9     DateTime      3

Answer 1

You can use a Temp Table to hold the Id of the records where N1 is equal to 9. You then select all the records from the Times table where the Id is what values are in the temp table plus one which is the very next record from the Id column the very next record after the N1 value of 9.

SQL Query

DROP TABLE IF EXISTS t1;
CREATE TEMPORARY TABLE t1 (SELECT id FROM numbers WHERE N1 = 9);
Select * FROM t1;

SELECT * FROM numbers
WHERE id IN (Select id+1 FROM t1);

Or

SELECT * FROM numbers WHERE id IN (SELECT ID+1 FROM numbers WHERE N1 = 9)

---

Confirmation

Select * from Times

With above query

---

Further Resources

• Temporary Table
• Create Table
• Drop Table

Answer 2

A better version of Moderator Impersonator answer would be:

SELECT * FROM numbers WHERE id IN (SELECT ID+1 FROM numbers WHERE N1 = 9)