0
  • I have 2 tables : users and paypal_transactions
  • For each user, we have an id (named user_id in paypal_transactions table)
  • A user may have several paypal_transactions. Relation one to many
  • I need to grab the latest transaction id (ordered by date_dt DESC) when I do my query

My current query :

SELECT `Transaction`.*, `User`.*, `Tipster`.`username`
FROM `pronostics_framework`.`users` AS `User`
LEFT JOIN `pronostics_framework`.`users` AS `Tipster` ON (`User`.`tipster_id` = `Tipster`.`id`)
LEFT JOIN `pronostics_framework`.`paypal_transactions` AS `Transaction` ON (`User`.`id` = `Transaction`.`user_id`)
ORDER BY `User`.`id` DESC
LIMIT 500

Currently with one transaction per user, it works fine. BTW with many transactions I still get the 1st entry from paypal_transactions table (the oldest, but I want the latest from now).

I did many tries, without success.

Thanks for your help !

zeflex
  • 1,487
  • 1
  • 14
  • 29
  • Please show what you tried. There are many answers here: http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group – Barmar Nov 08 '14 at 20:21
  • What I tried give errors or not what I am expecting, that's why I did not post them. Also it's not a duplicate as the tables joined are different. – zeflex Nov 08 '14 at 21:09
  • Yes, it's a duplicate. You just need to use the code on those answers in a subquery to get the latest post for each user, and join it with your `user` table. – Barmar Nov 08 '14 at 21:13
  • I already look for 2 hours on stackoverflow if something similar than me was there but I've found nothing. – zeflex Nov 08 '14 at 21:16

1 Answers1

1

Here you go:

SELECT `Transaction`.*, `User`.*, `Tipster`.`username`
FROM `pronostics_framework`.`users` AS `User`
LEFT JOIN `pronostics_framework`.`users` AS `Tipster` ON (`User`.`tipster_id` = `Tipster`.`id`)
LEFT JOIN (SELECT user_id, MAX(date_dt) AS max_date
            FROM `pronostics_framework`.paypal_transactions
            GROUP BY user_id) AS max_trans 
    ON User.id = max_trans.user_id
LEFT JOIN `pronostics_framework`.`paypal_transactions` AS `Transaction` 
    ON (max_trans.user_id = `Transaction`.`user_id` AND max_trans.max_date = Transation.date_dt)
ORDER BY `User`.`id` DESC
LIMIT 500

Another way, based on the first query in the first answer at Retrieving the last record in each group:

SELECT `Transaction`.*, `User`.*, `Tipster`.`username`
FROM `pronostics_framework`.`users` AS `User`
LEFT JOIN `pronostics_framework`.`users` AS `Tipster` ON (`User`.`tipster_id` = `Tipster`.`id`)
LEFT JOIN `pronostics_framework`.`paypal_transactions` AS `Transaction` ON Transaction.user_id = User.id
LEFT JOIN `pronostics_framework`.paypal_transactions AS Transactions1 
    ON Transactions1.user_id = Transactions.user_id AND Transactions1.user_id > Transactions.user_id
WHERE Transactions1.user_id IS NULL
ORDER BY `User`.`id` DESC
LIMIT 500
Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks Barmar ... I know it's not a free coding service but I always do some searches before posting a question there :) Btw, it works than, but no way to avoid this select inside the join to do what I need ? – zeflex Nov 08 '14 at 21:33
  • I've added another method. You should benchmark them to see which is more efficient. – Barmar Nov 08 '14 at 21:39
  • I've tried the second one and it returns me the same results than the initial one I've post . And speed is the same, so I will keep your first answer, thanks again ! – zeflex Nov 08 '14 at 21:56