SQL Server Performance Forum – Threads Archive

MAX in where clause

What is the best way to have MAX (or any aggregate function) in where clause? I am using the query below but performance is really bad. I would preferably like to not use subquery. SELECT B.BO_ID, B.CM_PURPOSE_TYPE_ID, D.DESCR, C.PHONE
FROM PS_BO_CM AS B
INNER JOIN PS_BO_CM_USE AS E ON B.BO_ID = E.BO_ID
AND B.PROFILE_CM_SEQ = E.PROFILE_CM_SEQ
INNER JOIN PS_CM AS C ON B.CM_ID = C.CM_ID
INNER JOIN PS_CM_PURP_TYPE AS D ON B.CM_TYPE_ID = D.CM_TYPE_ID
AND B.CM_PURPOSE_TYPE_ID = D.CM_PURPOSE_TYPE_ID
WHERE (C.CM_TYPE_ID = 2)
AND (C.PHONE <> ‘ ‘)
AND (E.CM_USE_START_DT =
(SELECT MAX(CM_USE_START_DT) AS Expr1
FROM PS_BO_CM_USE AS E1
WHERE (BO_ID = E.BO_ID)
AND (PROFILE_CM_SEQ = E.PROFILE_CM_SEQ)
AND (CM_USE_START_DT <= GETDATE()))
) AND (E.PRIMARY_IND = ‘Y’)

---

I might be missing something but this is one of the two common ways to get the latest entry <= a target date. The other one would be to turn this into a JOIN operation. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

Thanks Frank. Using a join greatly reduced running time.

---

]]>