CODEWITHSUNDEEP
mysqldateselectjoinmax
sc92
sc92

Reputation: 1

Finding most recent date of max purchase from two tables

I have two tables. First lists userid, amount - of the item purhased and the date - in which it was purchased. The second has userid, and the date as well as their current address. Each time a user changes his/her address a new row is inserted inside t2.

How do I find the most recent address of the user with the largest purchase made?

t1:
userid amount date

t2:
userid date address

Upvotes: 0

Views: 33

Answers (1)

splash58
splash58

Reputation: 26153

select t1.userid, t2.address 
   from t1 
   join 
      t2 on t2.userid=t1.userid  
   where amount = (select max(amount) from t1) 
   order by t2.`date` desc 
   limit 1

Demo on sqlfiddle

Upvotes: 1

Related Questions