Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

LAG on multiple rows?

SOUSOUApr 3 2025

Hi all,

I have a question and I wonder if any of you could give me some ideas on how to resolve this kind of queries.
To understand, the best is to give an exemple. Let's consider a table like this one:

CREATE TABLE orders (
id number, 
ordered number
);
INSERT INTO orders(id, ordered) VALUES(1, 10);
INSERT INTO orders(id, ordered) VALUES(2,  4);
INSERT INTO orders(id, ordered) VALUES(3, 12);
INSERT INTO orders(id, ordered) VALUES(4, 15);
INSERT INTO orders(id, ordered) VALUES(5,  1);

INSERT INTO orders(id, ordered) VALUES(6,  10);
INSERT INTO orders(id, ordered) VALUES(7,  20);
INSERT INTO orders(id, ordered) VALUES(8,  22);
INSERT INTO orders(id, ordered) VALUES(9,  26);

In this table I have a number of orders. First is an auto increment id. Orders follow this ID.

And we have an "ordered" field. That field is the amount of orders not yet processed. I would like to find if the last set of rows after an increase is more than 3.

In this exemple, row with ID 5 is 1. Then, we jump to 10. We see that there is an increase. The we go to 20, 22, 26. This is all increase and we have more than 3 consecutive rows. So, I should select these rows.

But, if in the middle of the rows, I had another decrease, I should not select them.

The idea is to see that orders are being processed faster than ordered.

So I thought we should start from the last row (bigger ID) and compare with previous rows until I get a value bigger than previous row. LAG can help. But how can I tell LAG to compare 3 rows? I don't know how to do that in SQL. Any suggestion? I'm still using Oracle 19c. So, an idea compatible to that version would be nice.

Thank you all

This post has been answered by Solomon Yakobson on Apr 4 2025
Jump to Answer
Comments
Post Details
Added on Apr 3 2025
15 comments
509 views