Search code examples
sqloracle-databaseoracle11g

How to limit rows on a window function

I want to get a result on one query.

with rws as (
select o.*, first_value(o.ACCT_ID) over(PARTITION by acct_id ORDER by acct_id asc) rn
from ACCT_PLAN o
)
select * from rws
where rn >= 10
and PLAN_STATUS_CD = 'ACTIVE'
order by acct_id;

So I need do get a following result. All rows are "grouped" by partition clause in the window function by acct_id in the CTE. In the main select I need to limit each ACCT_ID if they have more than 10 rows.

https://i.sstatic.net/K6rRL.png

https://i.sstatic.net/EDOGe.png

Solution

  • You need to COUNT rather then find the FIRST_VALUE if you want to return partitions with 10 or more rows:

    WITH rws AS (
  SELECT o.*,
         COUNT(*) OVER (PARTITION by acct_id ORDER by acct_id ASC) AS cnt
  FROM   ACCT_PLAN o
)
SELECT *
FROM   rws
WHERE  cnt >= 10
AND    PLAN_STATUS_CD = 'ACTIVE'
ORDER BY acct_id;

    If, instead, you want to limit a partition to the first 10 rows, and then to those who are active, then use ROW_NUMBER:

    WITH rws AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION by acct_id ORDER by acct_id ASC) AS cnt
  FROM   ACCT_PLAN o
)
SELECT *
FROM   rws
WHERE  rn <= 10
AND    PLAN_STATUS_CD = 'ACTIVE'
ORDER BY acct_id;

    Or if you want the first 10 active rows:

    WITH rws AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION by acct_id ORDER by acct_id ASC) AS cnt
  FROM   ACCT_PLAN o
  WHERE  PLAN_STATUS_CD = 'ACTIVE'
)
SELECT *
FROM   rws
WHERE  rn <= 10
ORDER BY acct_id;