Need help writing a query

[sptrsn]

I have two tables, that between them contain a complete record on property foreclosure data. (apn, adddress, sale date, open bid etc).

 

apn is unique in T1 and the data in T1 is the data that does NOT change. (ie apn and address)

 

In T2, we are storing the data that changes as time progresses. (ie sale date and open bid)

 

Each time it changes, we insert this newest data into T2, along with a date/time stamp and of course the related apn.

 

So, in T1, there is a unique record for each 'apn' with static data related to that property. (stands for assessors parcel number)

However, T2, there are several records with the same 'apn', differing values for sale_date and or open_bid, and a date/time when it was inserted.

 

I need a query that will join on T1.apn=T2.apn. However, I need the most recent matching T2.apn based on the 'update_date'.

 

I know this is not even close, but I have no idea how to get there from here.

 

select * from T1 join T2 on T1.apn=T2.apn 

 

Sure would appreciate any help you feel inclined to share.

Thanks

[blacknight]

select * from `T1` AS T1

JOIN `T2` AS T2 ON `T1`.`apn`=`T2`.`apn`

 

and depending on what your date for mat is

 

ORDER BY `T2`.update_date` SESC

[sptrsn]

Thanks so much for the reply.

 

I'll give it a go.

[sptrsn]

that didn't quite work.

 

the goal is to have one record for each apn joined with only the most recently updated matching apn in T2.

 

I was told that it would somehow involve sorting T2 then limit 1.

 

But if I add limit 1 to the end of that statement, I get one record total. I need one row for each unique apn.

 

Thanks.

[blacknight]

select * from `T2` AS T2

JOIN `T1` AS T1 ON `T2`.`apn`=`T1`.`apn`

ORDER BY `T2`.`update_date` DESC Limit 1;

 

try that

[sptrsn]

yea, that was what I tried, but it returns one single record.

 

I think that there is somehow a query within the query. Where in the inner query, we query T2 and sort and limit 1 per apn, then the it's joined with the matching apn from T1.

 

My coder had written this for something else and I got the impression that it would somehow be similar.

 

select * from `T1` where apn in (
select apn from `T2` 
group by apn having count(*) > 1)
order by apn

 

[PFMaBiSmAd]

See this link - http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

[sptrsn]

This looks very promising. I don't know why I couldn't find it before. I only looked for a day and half.

 

Thanks for that.