Showing list data transaction MAX(transaction_id) only by no_member in inner join SQL

Question

I use select with MAX but not effective, in simple way, I have data transaction relate with no_member

select a.no_member, b.trans_value, b.trans_id 
from table1 a 
inner join table2 b 
on a.no_member=b.no_member
order by a.no_member;

show

no_member       trans_value      trans_id
 111             100000           1
 111             200000           2
 111             300000           3
 222             150000           1
 222             200000           2
 333             300000           1
 333             100000           2
 333             500000           3
 333             100000           4

in my case, I just want show max(b.trans_id) for every one no_member

no_member       trans_value      trans_id
 111             300000           3
 222             200000           2
 333             100000           4

Need advice, thanks

the case maybe same, but I have 2 table relate with each other using inner join, that the different. I edit my title, adding "inner join". — agnanzakariya, Oct 17 '18 at 06:51

George Menoutis · Answer 1 · 2018-10-17T07:07:58.817

It appears table1 is not needed.

select top 1 (with ties) b.* 
from table2 b
order by dense_rank() over (partition by b.no_member order by b.trans_value desc) asc

Post-Op comment edit: I generally like to use two query "styles" when I want the highest/lowest/etc. of something for each line, and you can find both in the link your question has been marked as duplicate to (the link in the very top of this page).

The first one, is the one I wrote here, select top 1 with ties. It is Josh Gilfillan's answer in the duplicate link, and it is useful when you have only one table.

If you have 2+ tables, then the answer should be cross apply ( select top 1, like the answer of ddp. In your case, this would be something like:

select (some columns),q.trans_value,q.trans_id
from
    (some tables and joins)
    cross apply
    (
        select top 1 b.trans_value,b.trans_id
        from table2 b
        where b.no_member=a.no_member -- Here you add all "join conditions" with the external tables
        order by b.trans_value desc,b.trans_id asc -- what if two transactions have same value?
    )as q

I have more tables relate in complex condition, my question it's simplified from my case. It's should be use inner join to show the data. — agnanzakariya, Oct 17 '18 at 06:53

Showing list data transaction MAX(transaction_id) only by no_member in inner join SQL

1 Answers1