MYSQL - Get last result from each distinct id

Question

I trying to get the last result (balance) of each distinct account on the table (example bellow). I tried using GROUP BY but it returns the first rows finded.

My expected result is him to return me the rows 4 (last balance from account 2) and row 5 (last balance from account 1), instead it return the rows 1 and 3 (first balance from each account)

SQL used:

select * from sample_table group by account_id;

Sample Table:

+-------------+------------+
| id | account_id | balance|
+----+------------+--------+
|  1 |          1 | 100.00 |
|  2 |          1 | 150.00 |
|  3 |          2 |  50.00 |
|  4 |          1 | 130.00 |
|  5 |          2 |  70.00 |
+-------------+------------+

Assuming, `id` is the primary key. Can I know the id generation strategy used? — Naanavanalla, Aug 10 '18 at 20:47
id is auto_increment and the account_id is an foreign key from another table, also auto_increment. — ThiagoYou, Aug 10 '18 at 20:53
Go through this once https://stackoverflow.com/a/7745635/7725942 — Naanavanalla, Aug 10 '18 at 20:57
Use proper `GROUP BY`. How can you `SELECT *` in a `GROUP BY`??? In your example, for `account_id` 1, which `id` and `balance` is chosen??? It doesn't make sense. — Eric, Aug 10 '18 at 21:41
GROUP BY is not the point, I tried to use it but did not work. By the way, thanks you all by the attention. — ThiagoYou, Aug 10 '18 at 22:30

score 0 · Accepted Answer · answered Aug 10 '18 at 21:00

0

You can do it like this:

SELECT t1.*
FROM sample_table t1 LEFT JOIN sample_table t2
 ON (t1.account_id = t2.account_id AND t1.id < t2.id)
WHERE t2.id IS NULL;

answered Aug 10 '18 at 21:00

Sookie Singh

1,543
11
17

Worked just perfect for my case. Thanks – ThiagoYou Aug 10 '18 at 22:27

MYSQL - Get last result from each distinct id

1 Answers1