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 - 2013-05-17 : 14:35:30

I have this script

SELECT M.POLICY_DATE_TIME,* FROM MPL_EXPOSURE M
WHERE M.CLIENT_NUMBER = 2357
AND (M.POLICY_DATE_TIME =
(SELECT MAX (POLICY_DATE_TIME)
FROM MPL_EXPOSURE M2
WHERE M.CLIENT_NUMBER = M2.CLIENT_NUMBER
AND M.POLICY_DATE_TIME = M2.POLICY_DATE_TIME))

Why is it not get the latest record..When I run the script I get all the records for this client_number

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-17 : 14:52:57

It's becuase the sub-query is correlated.

SELECT 
	M.POLICY_DATE_TIME,
	* 
FROM 
	MPL_EXPOSURE M
WHERE 
	M.CLIENT_NUMBER = 2357
AND 
	M.POLICY_DATE_TIME = 
	(
		SELECT MAX (POLICY_DATE_TIME) 
		FROM MPL_EXPOSURE M2 
		WHERE M2.CLIENT_NUMBER = 2357
	)

-- OR


SELECT 
	M.POLICY_DATE_TIME,
	* 
FROM 
	MPL_EXPOSURE M
INNER JOIN
	(
		SELECT MAX (POLICY_DATE_TIME)  AS POLICY_DATE_TIME
		FROM MPL_EXPOSURE M2 
		WHERE M2.CLIENT_NUMBER = 2357
	) AS M2
	ON M.POLICY_DATE_TIME = M2.POLICY_DATE_TIME
WHERE 
	M.CLIENT_NUMBER = 2357

-- or... there are bunch of ways to do this..

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-17 : 14:54:43

quote:
Originally posted by divan

I have this script

SELECT M.POLICY_DATE_TIME,* FROM MPL_EXPOSURE M
WHERE M.CLIENT_NUMBER = 2357
AND (M.POLICY_DATE_TIME =
(SELECT MAX (POLICY_DATE_TIME)
FROM MPL_EXPOSURE M2
WHERE M.CLIENT_NUMBER = M2.CLIENT_NUMBER
AND M.POLICY_DATE_TIME = M2.POLICY_DATE_TIME))

Why is it not get the latest record..When I run the script I get all the records for this client_number

Remove the join on the POLICY_DATE_TIME:

SELECT M.POLICY_DATE_TIME,* FROM MPL_EXPOSURE M
WHERE M.CLIENT_NUMBER = 2357
AND (M.POLICY_DATE_TIME = 
(SELECT MAX (POLICY_DATE_TIME) 
FROM MPL_EXPOSURE M2 
WHERE M.CLIENT_NUMBER = M2.CLIENT_NUMBER 
AND M.POLICY_DATE_TIME = M2.POLICY_DATE_TIME))

Another way, which is logically equivalent and possibly faster is the following:

SELECT * FROM 
(                                  
	SELECT *,
		RANK() OVER(PARTITION BY CLIENT_NUMBER ORDER BY POLICY_DATE_TIME DESC) AS RN
	FROM 
		MPL_EXPOSURE
)s WHERE RN = 1

divan
Posting Yak Master

153 Posts

Posted - 2013-05-17 : 15:01:09

Thanks it worked great

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources