0

We have "transactions" table:

id  amount  txn_id  status  status_text
1    15     123456     0      pending
2    11     123123     0      pending
3    15     123456     1      complete
4    20     321456     0      pending
5    17     987456     0      pending
6    25     321321     0      pending
7    20     321456     1      complete
8    25     321321     1      complete

We need to get the rows with the highest status for each transaction id (txn_id). We are using this query:

SELECT id, amount, txn_id, status, status_text, MAX(status) as max_status 
FROM transactions  
GROUP BY txn_id 
ORDER BY id DESC

We are expecting:

id  amount  txn_id  status  status_text  max_status
8    25     321321     1      complete       1
7    20     321456     1      complete       1
5    17     987456     0      pending        0
3    15     123456     1      complete       1
2    11     123123     0      pending        0

But we are getting everything right except the "status_text" and the "status" columns which appear to be random. "max_status" is correct.

id  amount  txn_id  status  status_text  max_status
8    25     321321     1      complete       1
7    20     321456     0      pending        1
5    17     987456     0      pending        0
3    15     123456     0      complete       1
2    11     123123     0      pending        0

I am running out of ideas. How can we get the rows with the highest status for each transaction id?

thank you!

karlosuccess
  • 843
  • 1
  • 9
  • 25
  • If you would have turned on the strict mode in MySQL then you would see that your query fails. Because you select columns that you do not group nor aggregate and this is why you run into your problem. – juergen d Oct 29 '18 at 13:14
  • What is your MySQL server version ? – Madhur Bhaiya Oct 29 '18 at 13:23
  • @madhur probably less than 5.8, the above query would be invalid by default in newer versions. – Salman A Oct 29 '18 at 13:24

1 Answers1

0

You can try below using subquery

select a.id,a.amount,a.txn_id, a.`status`, status_text,mstatus from transaction a
inner join
(
select txn_id,max(`status`) as mstatus
from transaction group by txn_id
)b on a.txn_id=b.txn_id and a.`status`=b.mstatus
Fahmi
  • 37,315
  • 5
  • 22
  • 31