Some records are missing while selecting from table

Question

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)

Answer 1

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.

;WITH cte AS (SELECT MAX(WORKITEM_ID) AS max_workitem_id, PROCESS_INSTANCE_ID 
                  FROM WORKITEM
                  GROUP BY PROCESS_INSTANCE_ID
                 )
    SELECT WI.WORKITEM_ID AS WORKITEMID,
           PI.PROCESS_INSTANCE_ID PIID,WS.WS_LABEL AS WSNAME
    FROM WORKITEM AS WI
        INNER JOIN PROCESSINSTANCE AS [PI] ON WI.PROCESS_INSTANCE_ID = [PI].PROCESS_INSTANCE_ID
        INNER JOIN TASKASSIGNMENT_V4 AS TA ON TA.PROCESS_INSTANCE_ID = [PI].PROCESS_INSTANCE_ID
        INNER JOIN SDI_WIDGET_WORKSTEP AS WS ON WS.WS_NAME = WI.WORKSTEP_NAME
        INNER JOIN cte ON WI.WORKITEM_ID = cte.max_workitem_id AND cte.PROCESS_INSTANCE_ID = WI.PROCESS_INSTANCE_ID
    WHERE [PI].status IN ('PI_ACTIVATED','PI_COMPLETED')    
          AND WI.status IN ('I_ASSIGNED','I_AVAILABLE')