CODEWITHSUNDEEP
sqloracle-databasesql-order-by
Olilogu
Olilogu

Reputation: 1

Oracle SQL: Return previous n-rows order by date after condition

I need to return the previous 3 rows after condition of a set of data ordered by date. See bellow the query with a few data:

select invoice, cli_id, sale_date,processed from sales where cli_id = '490727' order by sale_date

I need to return previous 3 rows of before of certain condition ==> processed='W' Ex: want to know the 3 previous rows of PROCESSED = 'W' where invoice is 7995792. it will return: 7995793, 7995794 and 7995795

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

In Oracle 12C, you can use:

select s.*
from sales s
where s.sale_date < (select s2.sale_date
                     from sales s2
                     where s2.cli_id = s.cli_id and s2.processed = 'W'
                    ) and
      s.cli_id = 490727
order by s.sale_date desc
fetch first 3 rows only;

In earlier versions, you need a subquery:

select s.*
from (select s.*
      from sales s
      where s.sale_date < (select s2.sale_date
                           from sales s2
                           where s2.cli_id = s.cli_id and s2.processed = 'W'
                          ) and
            s.cli_id = 490727
      order by s.sale_date desc
     ) s
where rownum <= 3;

Upvotes: 1

Related Questions