Question 20262 in Mysql Question posted in
The official MySQL documentation can be found here.

Find Next row and Previous Row based on result in sql – Mysql

In this sample table fruits, I want to get previous row, current row and next row where high_low_adjstmnt < 0.

Table

id  name    low high    high_low_adjstmnt      volume
1   Apple   5   3         -2                   1000
2   Orange  6   9          3                   2000
3   Banana  13  17         4                   3000
4   Avocado 11  19         8                   4000
5   Berry   21  17        -4                   5000
6   Peach   7   9          2                   6000
7   Mango   9   14         5                   7000
8   Grape   18  11         7                   8000
9   Kiwi    14  13        -1                   9000

Desired output Result

id  name    low high    high_low_adjstmnt      volume
1   Apple   5   3         -2                   1000
2   Orange  6   9          3                   2000
4   Avocado 11  19         8                   4000
5   Berry   21  17        -4                   5000
6   Peach   7   9          2                   6000
8   Grape   18  11         7                   8000
9   Kiwi    14  13        -1                   9000

How could I get this result in query?

Tags:

2

Answers


  1. 0

    I think there’s a few ways to approach this. Perhaps, you can use LAG() and LEAD() functions to get the previous and next row value then use cte (or derived table) to filter them like this:

    WITH cte AS (
SELECT *,
       LAG(high_low_adjstmnt) OVER (ORDER BY id) PreviousRow,
       LEAD(high_low_adjstmnt) OVER (ORDER BY id) NextRow
   FROM Table1)

SELECT * 
  FROM cte
WHERE (high_low_adjstmnt < 0 
         OR PreviousRow < 0
         OR NextRow < 0);

    Demo fiddle

    Login or Signup to reply.
  2. 0

    You can do self-join to get previous, current and next rows using the condition t.id = t2.id - 1 or t.id = t2.id or t.id = t2.id + 1.

    select t2.*
from mytable t
inner join mytable t2 on t.id = t2.id - 1 or t.id = t2.id or t.id = t2.id + 1
where t.high_low_adjstmnt < 0

    Result :

    id  name    low high    high_low_adjstmnt   volume
1   Apple   5   3   -2  1000
2   Orange  6   9   3   2000
4   Avocado 11  19  8   4000
5   Berry   21  17  -4  5000
6   Peach   7   9   2   6000
8   Grape   18  11  7   8000
9   Kiwi    14  13  -1  9000

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search