Search code examples
sqljoinsubquery

Reduce subquery to join

I don't understand how to optimize my subquery:

SELECT *
FROM rp_clientAffectationHistory as T1
WHERE rp_clientAffectationHistoryID in (SELECT MAX(rp_clientAffectationHistoryID)
        FROM rp_clientAffectationHistory as T2
GROUP BY `rp_clientID`)
AND `rp_userID` = 57

I have many duplicate entries for the same "rp_clientID"; I want only records which have the latest rp_clientAffectationHistoryID.

Solution

  • What you need is something like:

    SELECT *
  FROM Rp_Clientaffectationhistory AS T1
left join Rp_Clientaffectationhistory as T2 on (T1.Rp_Clientid = T2.Rp_Clientid) and T1.Rp_Clientaffectationhistoryid < T2.Rp_Clientaffectationhistoryid
where T2.Rp_Clientaffectationhistoryid is null
and T1.Rp_Userid = 57

    Does it help?