Sql query to find the latest start date when the code was tagged to the employee

Question

I have an assignment table with the union code details as follows -

ASG

NUMBER      START_DATE              END_DATE                POSITION            UNION CODE        MANAGER
10          01-JAN-2006             17-AUG-2017             MANAGER             6790             Cyndy
10          18-AUG-2017             10-JUL-2018             SUPER MANAGER       6790             Cyndy
10          11-JUL-2018             31-12-4712              SUPER MANAGER       6791             Cyndy

10          01-JAN-2006             07-AUG-2017             associate           6790             Bali
10          08-AUG-2017             10-JUL-2019             sr. associate       6790             Bali
10          11-JUL-2019             31-12-4712              MANAGER             6790             Bali

I tweak the below query such that I can find the first date when the latest union code was tagged to the employee. So the output should look like -

NUMBER      START_DATE              POSITION            UNION CODE        MANAGER
10          11-JUL-2018             SUPER MANAGER       6791             Cyndy
10          01-JAN-2006             associate           6790             Bali

I am using the below query to find the latest row but I need the first date when the union code was tagged-

select NUMBER, START_dATE,POSITION,UNION_CODE,MANAGER from 
asg
where sysdate between start_Date and end_date

Answer 1

To get the latest union_code and then the earliest start_date for each number you can use the ROW_NUMBER analytic function:

SELECT *
FROM   (
  SELECT a.*,
         ROW_NUMBER() OVER (
           PARTITION BY "NUMBER" ORDER BY union_code DESC, start_date ASC
         ) AS rn
  FROM   asg a
)
WHERE  rn = 1;

Which, for the sample data:

CREATE TABLE ASG ("NUMBER", START_DATE, END_DATE, POSITION, UNION_CODE, MANAGER) AS
SELECT 10, DATE '2006-01-01', DATE '2017-08-17', 'MANAGER',       6790, 'Cyndy' FROM DUAL UNION ALL
SELECT 10, DATE '2017-08-18', DATE '2018-07-10', 'SUPER MANAGER', 6790, 'Cyndy' FROM DUAL UNION ALL
SELECT 10, DATE '2018-07-11', DATE '4712-12-31', 'SUPER MANAGER', 6791, 'Cyndy' FROM DUAL UNION ALL
SELECT 11, DATE '2006-01-01', DATE '2017-08-07', 'associate',     6790, 'Bali'  FROM DUAL UNION ALL
SELECT 11, DATE '2017-08-08', DATE '2019-07-10', 'sr. associate', 6790, 'Bali'  FROM DUAL UNION ALL
SELECT 11, DATE '2019-07-11', DATE '4712-12-31', 'MANAGER',       6790, 'Bali'  FROM DUAL;

(Assuming that you have 2 employees and they should have different NUMBERs)

Outputs:

NUMBER	START_DATE	END_DATE	POSITION	UNION_CODE	MANAGER	RN
10	2018-07-11 00:00:00	4712-12-31 00:00:00	SUPER MANAGER	6791	Cyndy	1
11	2006-01-01 00:00:00	2017-08-07 00:00:00	associate	6790	Bali	1

fiddle