SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SELECT MAX
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divan
Posting Yak Master

153 Posts

Posted - 05/17/2013 :  14:35:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/17/2013 :  14:52:57  Show Profile  Reply with Quote
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..
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 05/17/2013 :  14:54:43  Show Profile  Reply with Quote
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
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 05/17/2013 :  15:01:09  Show Profile  Reply with Quote
Thanks it worked great
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000