Retrieve first row of oracle dataset based on condition

Question

So I have a problem on which I am working for a while and i can't really figure out how to solve it. Basically I cannot imagine, from a technical point of view, how I could reach the desired output.

I created the following SELECT which retrieves around 1M rows:

SELECT
    b.date_id,
    b.subs_dim_id,
    b.subs_id,
    b.cust_dim_id,
    b.cust_id,
    c.cust_code,
    b.subs_status_reason_id,
    r.subs_status_reason_code,
    b.subs_status_id,
    s.subs_status_desc
FROM
         table1 b
    JOIN table2    r ON b.subs_status_reason_id = r.subs_status_reason_id
    JOIN table3            s ON b.subs_status_id = s.subs_status_id
    JOIN table4                   c ON b.cust_id = c.cust_id
WHERE
    b.cust_id IN (
        SELECT
            cust_id
        FROM
            table4
        WHERE
            cust_code IN (
                '0268634649',
                '0050800721',
                '0600155758'
            )
            AND trunc(valid_to_date) > trunc(sysdate)
    )
ORDER BY
    b.date_id ASC;

The thing is that I only want to retrieve a specific set of rows, based on a condition ... but I can't imagine how I could write the statement.

If I use the select as it is, I will receive the following output:

What I want instead is to retrieve only the first row for a certain ID in a certain Status: ( in this case for each ID from CUST_CODE having SUBS_STATUS_ID 7 for the first time)

Answer 1

If you are on 12c+ and your inner query allows index access for individual cust_id, you may use lateral join for this:

select t.*
from table4 t4
  cross apply (
    select *
    from <your_main_select_statement> b
    where b.cust_id = t4.cust_id
      and b.subs_status_id = 7
    order by b.date_id asc
    fetch first 1 rows only
  ) t
where t4.cust_code in (
  '0268634649',
  '0050800721',
  '0600155758'
)
  /*removed trunc, because if something > something2 is true, then trunc(something) > something2. But the first allows you to use partitions and indexes*/
  and t4.valid_to_date > trunc(sysdate) 

Some sample code below:

create table t_small
as
select level as id
from dual
connect by level < 5

create table t_big
as
select
  trunc(level / 10) as id
  , sysdate + level as dt
  , 'Level ' || level as name
from dual
connect by level < 100

create index ix_t_big on t_big(id, dt)

select /*+ gather_plan_statistics */ t.*
from t_small t1
  cross apply (
    select t2.*
    from t_big t2
    where t2.id = t1.id
    order by dt desc
    fetch first 1 rows only
  ) t

ID | DT        | NAME    
-: | :-------- | :-------
 1 | 19-JUL-21 | Level 19
 2 | 29-JUL-21 | Level 29
 3 | 08-AUG-21 | Level 39
 4 | 18-AUG-21 | Level 49

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

| PLAN_TABLE_OUTPUT                                                                                                                                         |
| :-------------------------------------------------------------------------------------------------------------------------------------------------------- |
| SQL_ID  7n7ruwgr1pdz6, child number 0                                                                                                                     |
| -------------------------------------                                                                                                                     |
| select /*+ gather_plan_statistics */ t.* from t_small t1   cross apply                                                                                    |
| (     select t2.*     from t_big t2     where t2.id = t1.id     order                                                                                     |
| by dt desc     fetch first 1 rows only   ) t                                                                                                              |
|                                                                                                                                                           |
| Plan hash value: 2940462511                                                                                                                               |
|                                                                                                                                                           |
| --------------------------------------------------------------------------------------------------------------------------------------------------------- |
| | Id  | Operation                               | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem | |
| --------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |   0 | SELECT STATEMENT                        |                 |      1 |        |      4 |00:00:00.01 |       5 |      1 |       |       |          | |
| |   1 |  NESTED LOOPS                           |                 |      1 |      4 |      4 |00:00:00.01 |       5 |      1 |       |       |          | |
| |   2 |   TABLE ACCESS FULL                     | T_SMALL         |      1 |      4 |      4 |00:00:00.01 |       2 |      1 |       |       |          | |
| |   3 |   VIEW                                  | VW_LAT_2D0B8FC8 |      4 |      1 |      4 |00:00:00.01 |       3 |      0 |       |       |          | |
| |*  4 |    VIEW                                 |                 |      4 |      1 |      4 |00:00:00.01 |       3 |      0 |       |       |          | |
| |*  5 |     WINDOW SORT PUSHED RANK             |                 |      4 |     10 |      4 |00:00:00.01 |       3 |      0 |  2048 |  2048 | 2048  (0)| |
| |   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| T_BIG           |      4 |     10 |     40 |00:00:00.01 |       3 |      0 |       |       |          | |
| |*  7 |       INDEX RANGE SCAN                  | IX_T_BIG        |      4 |     10 |     40 |00:00:00.01 |       2 |      0 |       |       |          | |
| --------------------------------------------------------------------------------------------------------------------------------------------------------- |
|                                                                                                                                                           |
| Predicate Information (identified by operation id):                                                                                                       |
| ---------------------------------------------------                                                                                                       |
|                                                                                                                                                           |
|    4 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=1)                                                                                           |
|    5 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T2"."DT") DESC )<=1)                                                                        |
|    7 - access("T2"."ID"="T1"."ID")                                                                                                                        |
|                                                                                                                                                           |

db<>fiddle here

Answer 2

If you use your current query as a CTE (or an inline view) and

• add another condition (to select only subs_status_id = 7)
• add another column (analytic function, to calculate the first appearance per date_id for each cust_code) ...
• ... and use that result as rn = 1

maybe you'll get what you want. Something like this:

WITH
   temp
   AS
      (SELECT b.date_id,
              b.subs_dim_id,
              b.subs_id,
              b.cust_dim_id,
              b.cust_id,
              c.cust_code,
              b.subs_status_reason_id,
              r.subs_status_reason_code,
              b.subs_status_id,
              s.subs_status_desc,
              --
              ROW_NUMBER ()
                 OVER (PARTITION BY c.cust_code ORDER BY b.date_id) rn           --> this
         FROM table1 b
              JOIN table2 r
                 ON b.subs_status_reason_id = r.subs_status_reason_id
              JOIN table3 s ON b.subs_status_id = s.subs_status_id
              JOIN table4 c ON b.cust_id = c.cust_id
        WHERE     b.cust_id IN
                     (SELECT cust_id
                        FROM table4
                       WHERE     cust_code IN
                                    ('0268634649', '0050800721', '0600155758')
                             AND TRUNC (valid_to_date) > TRUNC (SYSDATE))
              AND b.subs_status_id = 7                                           --> this
      )
  SELECT t.*
    FROM temp t
   WHERE rn = 1                                                                  --> this
ORDER BY date_id ASC;