CODEWITHSUNDEEP
phpmysql
yip
yip

Reputation: 123

SQL: select last pay time with money in MySQL

I have a table called "pay_logs", including user's payment logs,

1. Fields as followed:

================================================

id           | primary key, auto_increment
user_account | NOT NULL, NOT UNIQUE
money        | DEFAULT 0.00
time         | NOT NULL

================================================

2. Data in table

================================================

id | user_account | time | money
1 | Mary | 1480000008 | 10.00
2 | Mary | 1480000009 | 5.00
3 | John | 1480000001 | 2.00
4 | John | 1480000002 | 1.00

================================================

And I'd like to select user's LAST pay time with its money, so how can I do this in one SQL

Here is my SQL

select *, max(time)
from pay_logs
group by user_account;

3. And it's result:

================================================

id | user_account | time | money | max(time)
3 | John | 1480000001 | 2.00 | 1480000002
1 | Mary | 1480000008 | 10.00 | 1480000009

================================================

4. But it won't work as my wish:

What data I want is records with id 2 and 4, so any one help me? I want to do this in one SQL, just SQL, if SQL falied to do this job, any other solutions? THX TXH TXH ~~~

P.S.I am using PHP

Upvotes: 1

Views: 175

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Using your query as a basis, you need to use group by and a join:

select pl.*
from pay_logs pl join
     (select user_account, max(time) as maxt
      from pay_logs
      group by user_account
     ) ua
     on pl.user_account = ua.user_account and pl.time = ua.maxt;

Your query uses a MySQL (mis)feature that allows columns in the group by that are not in the group by clause. Unless you really know what you are doing, you should never do this.

Upvotes: 1

Related Questions