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-27 : 10:40:54

I HAVE WRITTEN THE FOLLOWING SCRIPT

SELECT POLICY_DATE_TIME,NUMBER,SPECIALTY FROM MPL_EXPOSURE M
WHERE CLIENT_NUMBER = 1027 AND M.POLICY_DATE_TIME = (SELECT MAX(POLICY_DATE_TIME) FROM MPL_EXPOSURE M3 WHERE M.POLICY_NUMBER = M3.POLICY_NUMBER)ORDER BY POLICY_DATE_TIME DESC

EXPECTING JUST ONE ROW SINCE I HAVE USED THE SELECT MAX STATEMENT BUT I AM GETTING THE FOLLOWING

POLICY_DATE_TIME NUMBER SPECIALTY
2012-06-01 13:04:56.000 1027 120
2012-05-30 10:36:37.000 1027 270
2011-06-03 15:14:11.340 1027 120
2010-05-28 16:53:13.810 1027 120
2009-09-16 15:55:41.000 1027 120
2009-09-14 15:01:42.930 1027 120
2009-05-20 08:53:05.000 1027 120
2008-08-05 12:48:45.740 1027 120

ANY IDEA????

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-27 : 10:53:18

Looks like each row corresponds to a different policy number. If you add the policy number also in the outer query you will see this:

SELECT
	POLICY_DATE_TIME,
	NUMBER,
	SPECIALTY,
	M.POLICY_NUMBER
FROM
	MPL_EXPOSURE M
WHERE
	CLIENT_NUMBER = 1027
	AND M.POLICY_DATE_TIME = (
	        SELECT
	        	MAX(POLICY_DATE_TIME)
	        FROM
	        	MPL_EXPOSURE M3
	        WHERE
	        	M.POLICY_NUMBER = M3.POLICY_NUMBER
	    )
ORDER BY
	POLICY_DATE_TIME DESC

One other thing to keep in mind is that if there is more than one row in the table for the same policy number with the same policy_date_time, you would get all those rows if that policy_date_time happened to be the MAX for that policy number.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-27 : 10:55:08

SELECT POLICY_DATE_TIME, NUMBER, SPECIALTY
FROM MPL_EXPOSURE M
WHERE CLIENT_NUMBER = 1027
AND M.POLICY_DATE_TIME = ( SELECT MAX(POLICY_DATE_TIME)
FROM MPL_EXPOSURE M3
WHERE M.NUMBER = M3.NUMBER )
ORDER BY POLICY_DATE_TIME DESC

--------------------------
http://connectsql.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources