Select a distinct and max vale from a table

Hi Experts

I am trying select rows form a table that are distinct and have a max date value ie

Account                                      Property               Value                Valuation_Date
Freehold Property - Cost B/F      12 Tysoe Street      370000.00      2008-09-30 00:00:00
Freehold Property - Cost B/F      12 Tysoe Street      370000.00      2008-12-31 00:00:00
Freehold Property - Cost B/F      12 Tysoe Street      370000.00      2009-03-31 00:00:00 *
Freehold Property - Cost B/F      21 Hamilton House      951000.00      2008-12-31 00:00:00
Freehold Property - Cost B/F      21 Hamilton House      951000.00      2008-09-30 00:00:00 *

I need to be able to select the stared rows based on the max date value and for each property
ie I only get one row for each property based on the max date

I have tried this

SELECT     vw_Accounts.name AS Account, properties.address AS Property, property_valuations.current_value,
                      property_valuations.valuation_date AS [Valuation Date]
FROM         properties INNER JOIN
                      property_valuations ON properties.property_id = property_valuations.proeprty_id INNER JOIN
                      vw_Accounts ON property_valuations.Account = vw_Accounts.account_id
WHERE     (properties.IsSold = 0) AND (property_valuations.valuation_date =
                          (SELECT     MAX(valuation_date) AS Expr1
                            FROM          property_valuations AS property_valuations_1))

however this returns the wrong records ie the max date only

Any ideas?
Thanks
21agdgcfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Something like this perhaps:
SELECT  a.name Account,
        p.[address] Property,
        v.current_value,
        v.valuation_date [Valuation Date]
FROM    properties p
        INNER JOIN property_valuations v ON p.property_id = v.proeprty_id
        INNER JOIN (SELECT  proeprty_id,
                            MAX(valuation_date)
                    FROM    property_valuations
                    GROUP BY proeprty_id
                   ) d ON v.property_id = d.proeprty_id
                          AND v.valuation_date = d.valuation_date
        INNER JOIN vw_Accounts a ON v.Account = a.account_id
WHERE   p.IsSold = 0

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
Incidentally, I suspect you may have mispelled proeprty_id in the property_valuations table.
0
21agdgcfAuthor Commented:
HI Thanks for that

You are right I have misspelt it however i get the error

No column was specified for column 2 of 'd'.No column was specified for column 2 of 'd'.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Rajkumar GsSoftware EngineerCommented:
You can achieve your expected results either by the way acperkins posted above. Or like below

In your query, the sub-query always returns the same value ie., the max of property_valuations.valuation_date. This cause wrong results in your query.

You have modify your query, the sub-query including a WHERE condition that will select only the max date of the particular property_id of each row only.

I have no actual tables to test with. Here I tried to modify your query including the WHERE condition in your sub-query. Check it.

If there is any addition condition to match, add it. Now add property_id as you want to get max date based on this field.

Raj


SELECT  vw_Accounts.name AS Account, 
		properties.address AS Property, 
		pv.current_value, 
        pv.valuation_date AS [Valuation Date]
FROM properties 
	INNER JOIN property_valuations pv ON properties.property_id = pv.property_id 
	INNER JOIN vw_Accounts ON pv.Account = vw_Accounts.account_id
WHERE	(properties.IsSold = 0) AND 
		(pv.valuation_date =
                  (SELECT     MAX(valuation_date) AS Expr1
						FROM          property_valuations AS property_valuations_1
							WHERE property_id = pv.property_id))

Open in new window

0
Anthony PerkinsCommented:
You are right.
This column:
MAX(valuation_date)
Should have aliased as follows:
MAX(valuation_date) valuation_date

 
0
21agdgcfAuthor Commented:
Hi acperkins

Many thanks for that

I think I undderstadn the logic to apply elsewhere now

 
0
21agdgcfAuthor Commented:
Again many thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.