How to get previous or next row in a given row in mysql using lead or lag?

Question

I've been reading on lead and lag functions but I still don't get it. My sql syntax is as follows:

SELECT description, lag(description) over (ORDER BY transid ASC) 
  FROM transaction 
 WHERE transid = 20;

It's not returning the desired data. I have a table where transid is the primary key in int, and a column called description. I think the syntax is wrong because I want to return the whole row (all the column data) and I think this just returns one column of the previous row. How do I do that? Anybody?

Return an entire previous/next row.

I have a table, transid is the primary key and the other columns are for example refpage, refline, description, accountcode, codetype. I enter a value of transid that is equal to 10 and I want to get the values of the row before 10, so that's 9, and all the columns that 9 has. Or for the next row I'll get the column values of 11. How do I get the data? The output is all the columns of a previous row or next row. The input is 10. — buencamino, Aug 08 '19 at 07:54
Possible duplicate of [How to get next/previous record in MySQL?](https://stackoverflow.com/questions/1446821/how-to-get-next-previous-record-in-mysql) — Sebastian Brosch, Aug 08 '19 at 08:00
https://stackoverflow.com/questions/9894092/select-previous-row-mysql — SAS, Aug 08 '19 at 08:00
Perhaps the WHERE clause has already removed the rows you want. — Salman A, Aug 08 '19 at 09:37

Tim Biegeleisen · Answer 1 · 2019-08-08T10:16:05.077

1

One option would be to use a subquery with LEAD to restrict to the desired previous record:

WITH cte AS (
    SELECT *, LEAD(transid) OVER (ORDER BY transid) transid_lead
    FROM transaction
)

SELECT *
FROM cte
WHERE transid_lead = 20;

edited Aug 08 '19 at 10:16

answered Aug 08 '19 at 07:53

Tim Biegeleisen

502,043
27
286
360

The query doesn't return any values.. just the column names.. maybe there's something wrong with mysql and lag/lead functions.. – buencamino Aug 08 '19 at 08:02
What value does the subquery return? – Tim Biegeleisen Aug 08 '19 at 08:09
just a null value. – buencamino Aug 08 '19 at 08:12
Then there is no "previous" value for `transid = 20`, or there is no `transid = 20` record to begin with. – Tim Biegeleisen Aug 08 '19 at 08:14
OK, is there any record where `transid` is less than 20? `SELECT COUNT(*) FROM transaction WHERE transid < 20` – Tim Biegeleisen Aug 08 '19 at 08:20
the value is 14 for count – buencamino Aug 08 '19 at 08:22
`WHERE` clause will remove all rows but one so there is no prev or next row. – Salman A Aug 08 '19 at 09:49
@SalmanA Thanks for cracking this problem, +1 to your answer. – Tim Biegeleisen Aug 08 '19 at 10:16

Salman A · Answer 2 · 2019-08-08T09:47:18.677

1

For previous row you can simply do this:

SELECT *
FROM transaction
WHERE transid < 20 
ORDER BY transid DESC
LIMIT 1

For next row you reverse the conditions:

SELECT *
FROM transaction
WHERE transid > 20 
ORDER BY transid ASC
LIMIT 1

edited Aug 08 '19 at 09:47

answered Aug 08 '19 at 09:39

Salman A

262,204
82
430
521

How to get previous or next row in a given row in mysql using lead or lag?

2 Answers2