The part you are commenting out is less restrictive than the outer query. For example, the max policy_date_time in the table ENDORSEMENT for a given NAME_AND_ADDRESS may have a different SHORT_CODE_1.
You can change the query like shown below:
select * from (
SELECT DISTINCT
POLICY_NUMBER,
NAME_AND_ADDRESS,
LTRIM(
ISNULL(C.FNAME1, '') + ' ' + ISNULL(C.INIT1, '') + ' ' + ISNULL(C.LNAME1, '')
+ ',' + ISNULL(C.SUFFIX1, '')
) AS INSURED,
CASE
WHEN C.CLIENT_TYPE = 'C' THEN 'GROUP'
ELSE 'INDIVIDUAL'
END AS GROUP_INDIVIDUAL,
EFFECTIVE_DATE,
TERMINATION_DATE,
DATE_1,
E3.DESCRIPTION AS TAIL_CODE,
PREMIUM_1,
ROW_NUMBER() OVER (PARTION BY POLICY_NUMBER,POLICY_NUMBER,NAME_AND_ADDRESS
ORDER BY POLICY_DATE_TIME DESC) AS RN
FROM ENDORSEMENT E
INNER JOIN EDIT_SHORT_CODE E3
ON E3.TBNAME = 'E0025_0604'
AND E3.NAME = 'TAIL_COV'
AND E3.CODE = SHORT_CODE_1
INNER JOIN CLIENT C
ON E.NAME_AND_ADDRESS = C.CLIENT_NUMBER
WHERE IDENTIFIER IN ('002', '01g')
--AND E.POLICY_DATE_TIME = (
-- SELECT MAX(POLICY_DATE_TIME)
-- FROM ENDORSEMENT E1
-- WHERE E1.NAME_AND_ADDRESS = E.NAME_AND_ADDRESS
-- )
AND POLICY_NUMBER NOT LIKE 'Q%'
AND NAME_AND_ADDRESS = 178
) s where RN = 1
ORDER BY
NAME_AND_ADDRESS
Select MAX not cooperating
Posted - 11/09/2012 : 09:06:06
