-1

I have the following MySQL syntax, it returns the max transaction_id for each user. It displays the correct max transaction_id for each user. However, it does not display the correct balance. Because for example it will display the correct max transaction_id but will display the balance from the lower transaction_id.

SELECT MAX(transaction_id) as transaction_id, username, balance FROM user_transactions GROUP BY username

Any ideas how to fix this?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
John Roberts
  • 93
  • 7
  • Does this answer your question? [How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?](https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-partition-by-another-column-in-mys) – FanoFN Sep 06 '21 at 00:55
  • And a lot more examples of that exists in SO. – FanoFN Sep 06 '21 at 00:56

2 Answers2

0

You query is malformed because the select columns and the group by columns are inconsistent.

There are many ways to solve this. One easy method is a correlated subquery:

select ut.*
from user_transactions ut
where ut.transaction_id = (select max(ut2.transaction_id)
                           from user_transactions ut2
                           where ut2.username = ut.username
                          );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I will do it this way

create table Test(id integer, title varchar(100), balance integer);

insert into Test(id, title, balance) values(1, "H1", 55);
insert into Test(id, title, balance) values(2, "H1", 3);
insert into Test(id, title, balance) values(3, "H1", 10);
insert into Test(id, title, balance) values(4, "H2", 23);
insert into Test(id, title, balance) values(5, "H2", 12);
insert into Test(id, title, balance) values(6, "H3", 33);
insert into Test(id, title, balance) values(7, "H3", 44);

commit;

select id, title, balance from (
select id, title, balance, row_number() over (partition by title order by id desc ) rn
from Test ) as t where rn = 1;

result:

id  title   balance
3   H1      10
5   H2      12
7   H3      44
bidwhapi
  • 1
  • 1