Need Client ID match in self join, but only bring me back the most current transaction (which is the highest transaction number)

Question

Can someone provide me with a query format? I know I need to join the table to itself, but to get the following I am totally lost.

Column A - "Client ID" is a hard number (no duplicates at all).

Column B - "Transactions" contains multiple transactions that have been done on the account of that Client ID. The higher the number of the transaction per client ID, the more current the transaction

I want SQL to give me the list of clients and then only return to me the highest Transaction Number ONLY. Each client should only pop back up with one transaction number.

I tried Select a.client ID

a.transactions

from eligibility A, eligibility B

where A.transaction > b.transaction

And that didn't work so I tried

a.client ID

a.transactions

from eligibility A, eligibility B

where .transactions = (select max(a.transactions) from eligibility B

where a.mbr_h_sid = s1.mbr_h_sid));

but SQL keep telling me it's not right either.

I actually have the entire query set, but it won't let me paste it. That's what I really would like someone to look at, because I think it would make a heck of a lot more sense.

http://dev.mysql.com/doc/refman/5.5/en/example-maximum-column-group-row.html — CBroe, Aug 13 '14 at 23:40

score 0 · Answer 1 · answered Aug 14 '14 at 00:20

I think your last query is trying to be:

select e.clientID, e.transactions
from eligibility e
where e.transactions = (select max(a.transactions)
                        from eligibility e2
                        where e.mbr_h_sid = e2.mbr_h_sid
                       );

This should do what you want.

Need Client ID match in self join, but only bring me back the most current transaction (which is the highest transaction number)

1 Answers1