Pick the earliest date record -Oracle sql

I have a doubt in Oracle SQl, I have to pick the earliest record based on date ranges. I have table A with

My resulting output should be (CLIENT_STATUS=2)

Please let me know how it can be achieved in oracle sql

If you want the earliest row in the set of rows with the latest client_status then, using MATCH_RECOGNIZE to perform row-by-row processing:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY sym_run_date DESC
  ALL ROWS PER MATCH
  PATTERN (^ {- (same_status*) -} next_row )
  DEFINE same_status AS client_status = NEXT(client_status)
)

or, using analytic functions:

SELECT SYM_RUN_DATE,
       CLIENT_NO,
       CLIENT_STATUS
FROM   (
  SELECT SYM_RUN_DATE,
         CLIENT_NO,
         CLIENT_STATUS,
         ROW_NUMBER() OVER (ORDER BY sym_run_date DESC)
           - ROW_NUMBER() OVER (PARTITION BY client_status ORDER BY sym_run_date DESC) AS grp
  FROM   table_name
)
WHERE   grp = 0
ORDER BY sym_run_date
FETCH FIRST ROW ONLY;

Which, for the sample data:

CREATE TABLE table_name (SYM_RUN_DATE, CLIENT_NO, CLIENT_STATUS) AS
SELECT DATE '2023-03-31', '00019872', 2 FROM DUAL UNION ALL
SELECT DATE '2023-03-30', '00019872', 2 FROM DUAL UNION ALL
SELECT DATE '2023-03-29', '00019872', 2 FROM DUAL UNION ALL
SELECT DATE '2023-03-28', '00019872', 2 FROM DUAL UNION ALL
SELECT DATE '2023-03-27', '00019872', 1 FROM DUAL UNION ALL
SELECT DATE '2023-03-26', '00019872', 1 FROM DUAL UNION ALL
SELECT DATE '2023-03-25', '00019872', 1 FROM DUAL UNION ALL
SELECT DATE '2023-03-24', '00019872', 2 FROM DUAL UNION ALL
SELECT DATE '2023-03-23', '00019872', 2 FROM DUAL UNION ALL
SELECT DATE '2023-03-22', '00019872', 2 FROM DUAL UNION ALL
SELECT DATE '2023-03-21', '00019872', 2 FROM DUAL UNION ALL
SELECT DATE '2023-03-20', '00019872', 2 FROM DUAL UNION ALL
SELECT DATE '2023-03-19', '00019872', 2 FROM DUAL UNION ALL
SELECT DATE '2023-03-18', '00019872', 1 FROM DUAL UNION ALL
SELECT DATE '2023-03-17', '00019872', 1 FROM DUAL UNION ALL
SELECT DATE '2023-03-16', '00019872', 1 FROM DUAL;

Both output:

fiddle

A convenient method to arrive at a single row that matches a minimum date with a date range is to use row_number() e.g:

SELECT
      SYM_RUN_DATE
    , CLIENT_NO
    , CLIENT_STATUS
FROM (
    SELECT
          SYM_RUN_DATE
        , CLIENT_NO
        , CLIENT_STATUS
        , row_number() OVER (ORDER BY SYM_RUN_DATE ASC) AS rn
    FROM tablea
    WHERE SYM_RUN_DATE > to_date('2023-03-27')
            AND SYM_RUN_DATE <= to_date('2023-04-23')
    ) d
WHERE rn = 1

Here the row_number() function starts at 1 for the oldest date and increments by 1 for each subsequent row. In the outer query we simply ask for only that row haveing that generated rn of 1. Note that is the order by that determines how the row numbering sequence is generated. An ascending order make the oldest date row 1, whereas a descending order would make the most recent date row 1. Also not that if there are more than one row with the same oldest date you will still only get 1 row returned by this method.

I don't particularly like this approach, but - at the moment, can't figure anything better.

Sample data:

SQL> select * From test order by sym_run_date desc;

SYM_RUN_DA CLIENT_N CLIENT_STATUS
---------- -------- -------------
2023-03-31 00019872             2
2023-03-30 00019872             2
2023-03-29 00019872             2
2023-03-28 00019872             2
2023-03-27 00019872             1
2023-03-26 00019872             1
2023-03-25 00019872             1
2023-03-24 00019872             2
2023-03-23 00019872             2

9 rows selected.

temp CTE finds the highest date per client for status = 1, while main query returns row whose date is larger than the one returned by the CTE, and whose status = 2.

SQL> with temp as
  2    (select client_no, max(sym_run_date) sym_run_date
  3     from test
  4     where client_status = 1
  5     group by client_no
  6    )
  7  select a.client_no, min(a.sym_run_date) sym_run_date, a.client_status
  8  from test a join temp b on a.client_no = b.client_no
  9  where a.sym_run_date > b.sym_run_date
 10    and a.client_status = 2
 11  group by a.client_no, a.client_status;

CLIENT_N SYM_RUN_DA CLIENT_STATUS
-------- ---------- -------------
00019872 2023-03-28             2

SQL>