CODEWITHSUNDEEP
sqlsql-serverrepeat
talendguy
talendguy

Reputation: 81

SQL query last transactions

enter image description here

You can't see on the image but I have many till_id numbers. (1,2,3,4,5).

What I want to do is just showing the last "trans_num" without repeating the till_id.

For example:

till_id    trans_num
1          14211
2          14333
3          14555

Upvotes: 1

Views: 62

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133380

You can use group by till_id in subselect 

Select a.till_id a.trans_num
from your_table as a
where (a.trans_date. a.till_id)  = (select max(b.trans_date), b-till_id
                              from your_table as b
                              group_by b.till_id
                            );

Upvotes: 0

Avi
Avi

Reputation: 1145

select till_id  ,trans_num, max(transdate) from tableA
group by till_id  ,trans_num

Filter the columns you need in outer query or write inner query in where condition

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270643

A typical way to do this is:

select t.*
from t
where t.trans_date = (select max(t2.trans_date)
                      from t t2 
                      where t2.till_id = t.till_id
                     );

Upvotes: 1

Related Questions