I am working on an OTBI report that uses Advanced SQL to create the analysis. The reason for using this method is specific to the type of report I am working to build, which is joining information from a pending worker relationship (worker assignment real time) with the allocated checklist table (checklist real time).

Below is the syntax for this report:

Select 	 Person_Number

,		 Person_Name

,		 Manager_Name

,		 Assignment_Number

,		 Assignment_Type

,		 Business_Unit

,		 Department

,		 Grade

,		 Job

,		 Legal_Employer

,		 Location

,		 Projected_Start_Date

,		 User_Name

,		 Category

,		 Checklist_Name

,		 Allocation_Date

,		 Allocated_Checklist_Status

,		 Completion_Date

,		 Document_Type

,		 Task_Name

,		 Owner_Name

,		 Task_Performer_Name

,		 Cleared_To_Hire_Status

,		 Allocated_Task_Identifier

,		 Assignment_Status_Type

,		 Allocated_Instance

From

(

Select 	A.s_10 Person_Number

,		A.s_11 Person_Name

,		A.s_7 Manager_Name

,		A.s_8 Assignment_Number

,		A.s_9 Assignment_Type

,		A.s_1 Business_Unit

,		A.s_2 Department

,		A.s_3 Grade

,		A.s_4 Job

,		A.s_5 Legal_Employer

,		A.s_6 Location

,		A.s_12 Projected_Start_Date

,		A.s_13 User_Name

,		A.s_14 Assignment_Status_Type

,		B.s_2 Category

,		B.s_3 Checklist_Name

,		B.s_1 Allocation_Date

,		B.s_6 Allocated_Checklist_Status

,		B.s_4 Completion_Date

,		B.s_7 Document_Type

,		B.s_9 Task_Name

,		B.s_8 Owner_Name

,		B.s_10 Task_Performer_Name

,		B.s_11 Cleared_To_Hire_Status

,		B.s_12 Allocated_Task_Identifier

,		B.s_13 Allocated_Instance

From

(

SELECT

"Worker"."Person Number" s_10,

"Worker"."Employee Name" s_11,

"Manager"."Name" s_7,

"Worker"."Assignment Number" s_8,

"Worker"."Assignment Type" s_9,

"Business Unit"."Business Unit Name" s_1,

"Department"."Department Name" s_2,

"Grade"."Grade Name" s_3,

"Job"."Job Name" s_4,

"Legal Employer"."Name" s_5,

"Location"."Worker Location Name" s_6,

"Worker"."Assignment Projected Start Date" s_12,

"Worker"."User Name" s_13,

"Worker"."Assignment Status Type" s_14

FROM "Workforce Management - Worker Assignment Real Time"

WHERE (DESCRIPTOR_IDOF("Worker"."Assignment Type") = 'P')

AND s_14 = 'Active'

)A,
(

SELECT

"Allocated Checklist"."Allocation Date" s_1,

"Allocated Checklist"."Category" s_2,

"Allocated Checklist"."Checklist Name" s_3,

"Allocated Checklist"."Completion Date" s_4,

"Allocated Checklist"."Person Number" s_5,

"Allocated Checklist"."Status" s_6,

"Checklist Task Details"."Document Type Name" s_7,

"Checklist Task Details"."Owner Person Name" s_8,

"Checklist Task Details"."Task Name" s_9,

"Checklist Task Details"."Task Performer Name" s_10,

"Checklist Task Details"."PER_PERSON_ALLOCATED_TASKS_DFF_CLEARED_TO_HIRE_" s_11,

"Checklist Task Details"."Allocated Task Identifier" s_12,

"Allocated Checklist"."Allocated Instance" s_13

FROM "Workforce Management - Checklist Real Time"

WHERE s_9 IN ('Cleared to Hire')

AND s_3 IN ('Pre-boarding Step', 'Resident Pre-boarding Step')

)B
Where A.s_10 = B.s_5

)OUTER_TAB

The challenge that I have encountered is I'd like for this analysis to return or filter only the MAX "Checklist Task Details"."Allocated Task Identifier" grouped by the "Checklist Task Details"."Task Name". This is because a user could have more than one of the same task (perhaps due to their previous employment), but the report always need to look at the latest information for their checklist.

What is the proper syntax that can achieve this desired result? Thanks.