I am trying to match the records having MAX(WORKITEM_ID)
but some PROCESS_INSTANCE_ID
are not coming though they are present in WORKITEM
table.
select WI.WORKITEM_ID as WORKITEMID,
PI.PROCESS_INSTANCE_ID PIID,WS.WS_LABEL as WSNAME
from WORKITEM WI,PROCESSINSTANCE PI,TASKASSIGNMENT_V4TA,
SDI_WIDGET_WORKSTEP WS
where WS.WS_NAME = WI.WORKSTEP_NAME and
TA.PROCESS_INSTANCE_ID = PI.PROCESS_INSTANCE_ID and
PI.status in ('PI_ACTIVATED','PI_COMPLETED')and
WI.PROCESS_INSTANCE_ID = PI.PROCESS_INSTANCE_ID
and WI.status in ('I_ASSIGNED','I_AVAILABLE')
and WI.WORKITEM_ID =
(select MAX(WORKITEM_ID)from (select WITEM.WORKITEM_ID from WORKITEM WITEM
where **WITEM.PROCESS_INSTANCE_ID = WI.PROCESS_INSTANCE_ID**) as WORKITEMID)
Best Answer
You didn't state which version of SQL Server you are using, but I'll assume it is 2005 or later, and therefore, will suggest using a CTE . And, as Skagalak mentioned, I've rewritten the joins.
I wasn't sure whether you wanted the max value from the whole WORKITEM table or if you wanted to group it by PROCESS_INSTANCE_ID. The example below groups the MAX(WORKITEM_ID). If you don't want that, just remove the GROUP BY clause from the CTE.
CAVEAT: I've checked for syntax, but since I couldn't test against a data set I give no guarantees that this will give the desired results. It is merely an example of how you might want to structure the query.