CODEWITHSUNDEEP
mysql
Veera
Veera

Reputation: 1

Scrollable Cursors in MySQL 8.0

We have a requirement to compare the current row/record with previous and next row values.

Cursors in MySQL is non-scrollable. so, we are iterating the table data with while loop, fetching the previous / next record with limit clause and comparing the values.

For 20k records, this logic is taking lot of time. Can anyone suggest and give example to iterate over table data, compare the current record values with previous and next record.

Thanks, Veera

Upvotes: 0

Views: 197

Answers (1)

Geoduck
Geoduck

Reputation: 9005

You can likely do this with a table JOIN. If you do not have an ID column that is useable you would need to add another column with the rank order and then add a index to it. There are answers on here on how to add the rank column.

But, let's assume your ID column is sufficient: no gaps, proper order:

SELECT main.*, prev.*, next.* FROM
    mytable as main
    left join prev on main.ID = prev.ID+1
    left join next on main.ID = next.ID-1

You can then add to the query the logic or conditions based on comparing main.column to prev.column and next.column.

Upvotes: 1

Related Questions