CODEWITHSUNDEEP
sqloracle-database
Pat
Pat

Reputation: 2257

Oracle SQL - select last 3 rows after a specific row

Below is my data:

enter image description here

My requirement is to get the first 3 consecutive approvals. So from above data, ID 4, 5 and 6 are the rows that I need to select. ID 1 and 2 are not eligible, because ID 3 is a rejection and hence breaks the consecutive condition of actions. Basically, I am looking for the last rejection in the list and then finding the 3 consecutive approvals after that.

Also, if there are no rejections in the chain of actions then the first 3 actions should be the result. For below data:

enter image description here

So my output should be ID 11, 12 and 13.

And if there are less than 3 approvals, then the output should be the list of approvals. For below data:

enter image description here

output should be ID 21 and 22.

Is there any way to achieve this with SQL query only - i.e. no PL-SQL code?

Upvotes: 0

Views: 925

Answers (2)

Popeye
Popeye

Reputation: 35910

You can use IN and ROW_NUMBER analytical function as following:

SELECT * FROM
    (  SELECT
            T.*,
            ROW_NUMBER() OVER(ORDER BY Y.ACTION_AT) AS RN
        FROM YOUR_TABLE Y
        WHERE Y.ACTION = 'APPROVE'
          AND Y.ACTION_AT >= COALESCE(
               (SELECT MAX(YIN.ACTION_AT)
                FROM YOUR_TABLE YIN
                WHERE YIN.ACTION = 'REJECT'
               ), Y.ACTION_AT) )
WHERE RN <= 3;

Cheers!!

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Here is one method that uses window functions:

  • Find the first row where there are three approvals.
  • Find the minimum action_at among the rows with three approvals
  • Filter
  • Keep the three rows you want

This version uses fetch which is in Oracle 12+:

select t.*
from (select t.*,
             min(case when has_approval_3 = 3 then action_at end) over () as first_action_at
      from (select t.*,
                   sum(case when action = 'APPROVAL' then 1 else 0 end) over (order by action_at rows between current row and 2 following) as has_approval_3
            from t
           ) t
     ) t
where action = 'APPROVAL' and
      (action_at >= first_action_at or first_action_at is null)
order by action_at
fetch first 3 rows only;

Upvotes: 2

Related Questions