SELECT MAX - 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

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-08-10 : 07:46:06

I am confused or am wondering why the following script does not select only one record but instead selects all the records.

SELECT POLICY_DATE_TIME,* FROM POLICY P
WHERE P.POLICY_DATE_TIME = (SELECT MAX (POLICY_DATE_TIME) FROM POLICY P2 WHERE P.POLICY_NUMBER = P2.POLICY_NUMBER AND P.POLICY_DATE_TIME = P2.POLICY_DATE_TIME) AND P.POLICY_NUMBER = 'XX 100'

here is the result

XX 100 2007-11-07 15:31:55.000
XX 100 2008-02-08 12:31:55.000
XX 100 2008-11-14 16:42:27.000
XX 100 2008-12-25 14:04:11.760
XX 100 2009-11-13 09:06:48.000
XX 100 2010-08-16 13:11:52.000
XX 100 2011-08-11 09:13:35.000

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 07:55:47

Remove the join on POLICY_DATE_TIME from the subquery:

SELECT POLICY_DATE_TIME,* FROM POLICY P
WHERE P.POLICY_DATE_TIME = (SELECT MAX (POLICY_DATE_TIME) FROM POLICY P2 WHERE P.POLICY_NUMBER = P2.POLICY_NUMBER
 AND P.POLICY_DATE_TIME = P2.POLICY_DATE_TIME) AND P.POLICY_NUMBER = 'XX 100'

divan
Posting Yak Master

153 Posts

Posted - 2012-08-10 : 08:16:08

Thanks Sunitabeck.... I really appreciate all your help...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources