CODEWITHSUNDEEP
mysqlsql
Wasim
Wasim

Reputation: 5113

MySQL Selecting the latest payments from a table with a list of users with multiple payments

I have a table full of user payments. Each user can have multiple payments. I'm looking to select the latest payment for each user. 

SELECT DATE_FORMAT(FROM_UNIXTIME(MAX(p.date)), "%D %M %Y") as last_payment, p.user_id, p.amount, p.period, p.txn_id, u.name 
FROM payments as p
INNER JOIN users as u
ON u.id = p.user_id
GROUP BY p.user_id
ORDER BY p.date DESC

Now this seems to work fine, the last_payment field is indeed the date of the users latest payment, the only problem is that the other payment fields like p.txn_id and p.amount do not necessarily correspond to the same row as the users last payment, but from a previous payment.

How do I make sure that when i'm choosing the MAX(p.date) it's also choosing the other fields from that same row.

Appreciate the help.

Upvotes: 0

Views: 1717

Answers (4)

Strawberry
Strawberry

Reputation: 33945

SELECT u.*
     , x.*
  FROM payments x
  JOIN (SELECT user_id,MAX(date) max_date FROM payments GROUP BY user_id) y
    ON y.user_id = x.user_id
   AND x.date = y.max_date
  JOIN users u
    ON u.id = x.user_id;

Upvotes: 1

eggyal
eggyal

Reputation: 125865

You are looking for the groupwise maximum; the most common way is to join the payments table back to the grouped results:

SELECT   DATE_FORMAT(FROM_UNIXTIME(p.date), '%D %M %Y') AS last_payment,
         p.user_id, p.amount, p.period, p.txn_id, u.name
FROM     payments AS p NATURAL JOIN (
           SELECT   user_id, MAX(date) date
           FROM     payments
           GROUP BY user_id
         ) t JOIN users AS u ON u.id = p.user_id
ORDER BY p.date DESC

Note that the only reason your original query worked at all (and didn't throw an error) is because of MySQL's non-standard extensions to GROUP BY:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Upvotes: 1

i100
i100

Reputation: 4666

try something like this

SELECT DATE_FORMAT(FROM_UNIXTIME(p.date), "%D %M %Y") as last_payment
       , p.user_id, p.amount, p.period, p.txn_id, u.name 
FROM payments p, users u
WHERE u.id = p.user_id 
  and exists (SELECT max(p1.date) from payments p1 
               where p1.user_id = p.user_id 
              having max(p1.date)=p.date 
               group by p.user_id,  p1.date )
ORDER BY p.date DESC

Upvotes: 0

Bere
Bere

Reputation: 1747

SELECT DATE_FORMAT(p.date, "%D %M %Y") as last_payment, p.user_id, p.amount, p.period, p.txn_id, u.name 
FROM payments as p where 
INNER JOIN users as u
ON u.id = p.user_id
GROUP BY p.user_id having FROM_UNIXTIME(p.date)=FROM_UNIXTIME(MAX(p.date))
ORDER BY p.date DESC

Upvotes: 0

Related Questions