Reduce subquery to join

Question

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.

What's error with this query? – TechDo May 20 '13 at 07:39 — TechDo, May 20 '13 at 07:39

score 1 · Accepted Answer · edited Dec 06 '13 at 09:56

1

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?

edited Dec 06 '13 at 09:56

Matthias

3,582
2
30
41

answered Dec 06 '13 at 09:36

wraith

351
3
18

Also can see http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column?rq=1 – wraith Dec 06 '13 at 09:39

score 0 · Answer 2 · answered May 20 '13 at 07:47

I guess you want to get only one entry for every rp_clientID -

The one thing you missed is to check rp_clientID inside the inner query -

SELECT *
  FROM Rp_Clientaffectationhistory AS T1
 WHERE Rp_Clientaffectationhistoryid IN
       (SELECT MAX(Rp_Clientaffectationhistoryid)
          FROM Rp_Clientaffectationhistory AS T2
         WHERE T1.Rp_Clientid = T2.Rp_Clientid
         GROUP BY Rp_Clientid)
   AND Rp_Userid = 57

is it what you were looking for?

Well, my query is working fine. I only want to replace my subquery by a JOIN :) — IsraGab, May 20 '13 at 08:11

score 0 · Answer 3 · answered May 20 '13 at 07:50

0

For Sql server, try:

SELECT * FROM(
    SELECT *, ROW_NUMBER() over (partition by rp_userID order by rp_clientAffectationHistoryID desc) Rnum 
    FROM rp_clientAffectationHistory
    WHERE rp_userID = 57
)x
WHERE Rnum=1

answered May 20 '13 at 07:50

TechDo

18,398
3
51
64

Reduce subquery to join

3 Answers3