Pick the earliest date record -Oracle sql

Question

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

SYM_RUN_DATE	CLIENT_NO	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
2023-03-22	00019872	2
2023-03-21	00019872	2
2023-03-20	00019872	2
2023-03-19	00019872	2
2023-03-18	00019872	1
2023-03-17	00019872	1
2023-03-16	00019872	1

My resulting output should be (CLIENT_STATUS=2)

SYM_RUN_DATE	CLIENT_NO	CLIENT_STATUS
2023-03-28	00019872	2

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

If I rephrase your question, Do you need the minimum SYM_RUN_DATE for CLIENT_STATUS = 2 after maximum SYM_RUN_DATE for CLIENT_STATUS <> 2? — Ankit Bajpai, Jun 05 '23 at 07:05
[From stackoverflow help](https://stackoverflow.com/help/how-to-ask): **DO NOT post images of code, data, error messages, etc**. —copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. For more information please see the Meta FAQ entry [Why not upload images of code/errors when asking a question?](https://meta.stackoverflow.com/a/285557) — Paul Maxwell, Jun 05 '23 at 07:15
Your condition is not clear. To me `2023-03-19` would be the earliest date where `CLIENT_STATUS=2` — Wernfried Domscheit, Jun 05 '23 at 07:19
here is an already answered question similar to yours: https://stackoverflow.com/questions/6749369/oracle-datetime-in-where-clause — MasterSansai, Jun 05 '23 at 07:21

score 1 · Accepted Answer · answered Jun 05 '23 at 07:46

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:

SYM_RUN_DATE	CLIENT_NO	CLIENT_STATUS
2023-03-28 00:00:00	00019872	2

fiddle

Paul Maxwell · Answer 2 · 2023-06-05T08:51:04.003

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.

Hm, that returns wrong result (27th instead of 28th of March). — Littlefoot, Jun 05 '23 at 07:47
Adjust the date range, when I answered the question was very vague — Paul Maxwell, Jun 05 '23 at 08:49

score 0 · Answer 3 · answered Jun 05 '23 at 07:45

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>

Pick the earliest date record -Oracle sql

3 Answers3