asked on

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

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

21agdgcf

ASKER

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'.

Rajkumar Gs

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

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

21agdgcf

ASKER

Hi acperkins

Many thanks for that

I think I undderstadn the logic to apply elsewhere now

21agdgcf

ASKER

Again many thanks