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?
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
Anthony PerkinsConnect With a Mentor Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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 PerkinsConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.