Select Max not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Max not working

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-09-12 : 08:46:15

I have the following script( I have tested this with and without the DISTINCT)

SELECT ---DISTINCT
T.POLICY_NUMBER,
P1.POLICY_DATE_TIME,
T.OPCO_INSURED_NUM

FROM #TEMP1 T
INNER JOIN POLICY P1 ON T.POLICY_NUMBER = P1.POLICY_NUMBER AND P1.POLICY_DATE_TIME = (SELECT MAX(POLICY_DATE_TIME) FROM POLICY P2 WHERE P1.POLICY_NUMBER = P2.POLICY_NUMBER AND P1.POLICY_DATE_TIME = P2.POLICY_DATE_TIME)

ORDER BY OPCO_INSURED_NUM, T.POLICY_NUMBER

and I get the following should I not be just getting the latest record when selecting SELECT MAX

CC 10666 4/8/05 3:28 PM 100
CC 10666 4/10/07 2:25 PM 100
CC 11674 4/10/06 9:44 AM 100
CC 11674 7/13/06 2:30 PM 100
CC 11674 7/13/06 2:46 PM 100
CC 11674 9/2/06 3:08 PM 100
CC 11674 9/22/06 2:21 PM 100
CC 11674 4/17/07 12:31 PM 100
CC 11674 2/29/08 1:12 PM 100
CC 11674 3/28/08 1:27 PM 100
CC 11674 4/3/08 1:31 PM 100
CC 11674 4/3/08 1:39 PM 100
CC 11674 4/28/08 3:03 PM 100
CC 11674 1/8/09 1:01 PM 100

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-12 : 09:14:18

Remove the join condition on policy_date_time in the inner query.

SELECT ---DISTINCT 
       T.POLICY_NUMBER,
       P1.POLICY_DATE_TIME,
       T.OPCO_INSURED_NUM
FROM   #TEMP1 T
       INNER JOIN POLICY P1
            ON  T.POLICY_NUMBER = P1.POLICY_NUMBER
            AND P1.POLICY_DATE_TIME = (
                    SELECT MAX(POLICY_DATE_TIME)
                    FROM   POLICY P2
                    WHERE  P1.POLICY_NUMBER = P2.POLICY_NUMBER
                           AND P1.POLICY_DATE_TIME = P2.POLICY_DATE_TIME
                )
ORDER BY
       OPCO_INSURED_NUM,
       T.POLICY_NUMBER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 11:02:49

whats the datatype of P1.POLICY_DATE_TIME? seems like you're using a character based data type

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources