My table has duplicated property addresses. I want to pick up latest records, group by property address which means group by should only pick those addresses which are order by date desc and not asc:
Query is:
SELECT p.property_address, oh.dates, oh.statuss FROM property p INNER JOIN operations_history oh ON ((p.market_ref=oh.mar_ref)) WHERE ((oh.statuss IN(‘Offer Rejected’,‘Decreased Rejected5’)) OR (oh.statuss IN(’ Pending’,‘Increased Pending’))) AND oh.activity_type IN(‘Pending’,‘Sale Agreed’) AND oh.dates like ‘2015-05%’ group by p.property_address,oh.dates order by oh.dates desc
You need a two step query here. NOTE: this assumes you’re using a true datetime field and not straight date, which will make this request nearly impossible.
Step 1 will pull the property_address and the MAX(dates) for the property
Step 2 will pull the information you’re pulling in above, but limit it to the property address and date combinations which show up in step 1.
SELECT p.property_address
, oh.dates
, oh.statuss
FROM property p
INNER
JOIN ( SELECT mar_ref
, MAX(dates) AS latest
FROM operations_history
WHERE statuss
IN ('Offer Rejected'
,'Decreased Rejected5'
,' Pending'
,'Increased Pending')
AND activity_type
IN ('Pending'
,'Sale Agreed')
AND dates >= '2015-05-01'
AND dates < '2015-06-01'
GROUP
BY mar_ref ) AS x
ON x.mar_ref = p.market_ref
INNER
JOIN operations_history oh
ON oh.mar_ref = x.mar_ref
AND oh.dates = x.latest