CODEWITHSUNDEEP
mysqlsql
Utpal Sarkar
Utpal Sarkar

Reputation: 432

how to get next/prev row from mysql by group

I have a table on MySQL like this:

ID    Name    Group
1     One      A
2     Two      B
3     Three    A
4     Fore     C
5     Five     B
6     Six      A
7     Seven    B

I want to get the previous row/ next row in same group from my selected row. Like if I have selected row with ID=5, now how can I get the same group previous row(ID=2) when I haven't any information about the row and same with next row(ID=7).

Upvotes: 1

Views: 656

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You are looking for LEAD or LAG with Windows function, but it's was supported mysql higher version than 8.0. so you can instead write a subquery on select

look like this.

TestDLL

CREATE TABLE T(
   ID int,
   Name VARCHAR(100),
   `Group` VARCHAR(5)
);

INSERT INTO T VALUES (1,'One','A');
INSERT INTO T VALUES (2,'Two','B');
INSERT INTO T VALUES (3,'Three','A');
INSERT INTO T VALUES (4,'Fore','C');
INSERT INTO T VALUES (5,'Five','B');
INSERT INTO T VALUES (6,'Six','A');
INSERT INTO T VALUES (7,'Seven','B');

Query

select *,IFNULL((
          SELECT t2.ID
          FROM T t2 
          WHERE t1.Group = t2.Group and t1.ID > t2.ID 
          ORDER BY t2.ID DESC
          LIMIT 1
         ),t1.ID)previousID
         ,IFNULL((
          SELECT t2.ID
          FROM T t2 
          WHERE t1.Group = t2.Group and t1.ID < t2.ID 
          ORDER BY t2.ID 
          LIMIT 1
         ),t1.ID) nextID
from T t1

[Results]:

| ID |  Name | Group | previousID | nextID |
|----|-------|-------|------------|--------|
|  1 |   One |     A |          1 |      3 |
|  2 |   Two |     B |          2 |      5 |
|  3 | Three |     A |          1 |      6 |
|  4 |  Fore |     C |          4 |      4 |
|  5 |  Five |     B |          2 |      7 |
|  6 |   Six |     A |          3 |      6 |
|  7 | Seven |     B |          5 |      7 |

If your mysql support windows function, you can try this.

select *,
  LAG(ID)previousID,
  LEAD(ID) nextID
from T

sqlfiddle

Upvotes: 2

Related Questions