Max row required for each group-CodePudding

There is 1 table wherein all these records are maintained for 1 employee. Due to one more column in this table which is not shown in this example the person has multiple rows for even 1 job , i.e. 2 rows for job J1 , 3 for Job J2 and again 2 rows for job J1 in order. The person switches from job J1 to J2 and then again J1.How to extract the required rows whenever a switch happens as I have to extract the max of each row when job is changed. I used group by in subquery of main query to extract the rows but then if the person switches back to same job J1 after going for J2 it doesn't work as it treats all the jobs in J1 in one group which is not correct. Please help me.

StDt             EdDt           Job      Required_Rows
1-Jan-21      31-Jan-21        J1          N
1-Feb-21      30-Jun-21        J1          Y
1-Jul-21      30-Jul-21        J2          N
1-Aug-21      15-Aug-21        J2          N
16-Aug-21     31-Aug-21        J2          Y
1-Sep-21      1-Nov-21         J1          N
2-Nov-21      31-Dec-21        J1          Y

CodePudding user response：

From Oracle 12, you can useMATCH_RECOGNIZE:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY stDt
  ALL ROWS PER MATCH
  PATTERN ({- same* -} same )
  DEFINE
    same AS FIRST(job) = job
);

In earlier versions, you can use the LEAD analytic function:

SELECT StDt,
       EdDt,
       Job
FROM   (
  SELECT t.*,
         LEAD(job) OVER (ORDER BY StDt) AS next_job
  FROM   table_name t
)
WHERE  next_job IS NULL
OR     next_job != job;

Which, for the sample data:

CREATE TABLE table_name (StDt, EdDt, Job) AS
SELECT DATE '2021-01-01', DATE '2021-01-31', 'J1' FROM DUAL UNION ALL
SELECT DATE '2021-02-01', DATE '2021-06-30', 'J1' FROM DUAL UNION ALL
SELECT DATE '2021-07-01', DATE '2021-07-31', 'J1' FROM DUAL UNION ALL
SELECT DATE '2021-08-01', DATE '2021-08-15', 'J2' FROM DUAL UNION ALL
SELECT DATE '2021-08-16', DATE '2021-08-31', 'J2' FROM DUAL UNION ALL
SELECT DATE '2021-09-01', DATE '2021-11-01', 'J1' FROM DUAL UNION ALL
SELECT DATE '2021-11-01', DATE '2021-12-31', 'J1' FROM DUAL

Both output:

STDT EDDT JOB

2021-07-01 00:00:00 2021-07-31 00:00:00 J1

2021-08-16 00:00:00 2021-08-31 00:00:00 J2

2021-11-01 00:00:00 2021-12-31 00:00:00 J1

STDT	EDDT	JOB
2021-07-01 00:00:00	2021-07-31 00:00:00	J1
2021-08-16 00:00:00	2021-08-31 00:00:00	J2
2021-11-01 00:00:00	2021-12-31 00:00:00	J1

db<>fiddle here