CODEWITHSUNDEEP
mysqlsqllaglead
buencamino
buencamino

Reputation: 101

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

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.

Upvotes: 1

Views: 1797

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522119

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;

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272256

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

Upvotes: 1

Related Questions