SQL SELECT to retrieve last transaction Row per Customer

[lpowell7]

I need a SELECT statement that will return the latest transaction row per Customer.
Below is a test database, with test data, followed by 3 select statements. None of the SELECT statements are quite what I need.
Any help would be much appreciated.

CREATE TABLE CUST
(Cust_ID INTEGER generated by default as identity primary key,
OrigDate DATE,
Cust_Name VARCHAR(50),
Cust_Type VARCHAR(15),
Cust_City VARCHAR(18),
Cust_State VARCHAR(2));

insert into CUST
(Cust_ID, Cust_Name, OrigDate, Cust_Type, Cust_City, Cust_State)
values(default, 'John Doe', '2012-03-25', 'Residential', 'Orlando', 'FL');

insert into CUST
(Cust_ID, Cust_Name, OrigDate, Cust_Type, Cust_City, Cust_State)
values(default, 'Sam Sam Roofing', '2012-03-25', 'Commercial', 'Atlanta', 'GA');

CREATE TABLE CUSTTRAN
(PID BIGINT generated by default as identity primary key,
TranDate DATE,
Cust_ID INTEGER,
Tran_Type VARCHAR(1),
Tran_Amt DECIMAL(13,2),
Pmt_Type VARCHAR(10));

ALTER TABLE CUSTTRAN
ADD CONSTRAINT FK_CUST_ID FOREIGN KEY (CUST_ID)
REFERENCES CUST (CUST_ID);

insert into CUSTTRAN
(PID, TranDate, Cust_ID, Tran_Type, Tran_Amt, Pmt_Type)
values(default, '2012-03-25', 1, 'A', 1320.44, 'VISA');

insert into CUSTTRAN
(PID, TranDate, Cust_ID, Tran_Type, Tran_Amt, Pmt_Type)
values(default, '2013-05-01', 1, 'A', 2547.52, 'DISCOVER');

insert into CUSTTRAN
(PID, TranDate, Cust_ID, Tran_Type, Tran_Amt, Pmt_Type)
values(default, '2013-05-01', 1, 'A', 3225.03, 'DISCOVER');


insert into CUSTTRAN
(PID, TranDate, Cust_ID, Tran_Type, Tran_Amt, Pmt_Type)
values(default, '2012-11-12', 2, 'A', 524.00, 'Ck# 3478');

insert into CUSTTRAN
(PID, TranDate, Cust_ID, Tran_Type, Tran_Amt, Pmt_Type)
values(default, '2012-11-12', 2, 'A', 423.22, 'Ck# 3479');

insert into CUSTTRAN
(PID, TranDate, Cust_ID, Tran_Type, Tran_Amt, Pmt_Type)
values(default, '2013-07-21', 2, 'B', 1300.40, 'MASTERCARD');

insert into CUSTTRAN
(PID, TranDate, Cust_ID, Tran_Type, Tran_Amt, Pmt_Type)
values(default, '2013-07-21', 2, 'B', 2500.50, 'MASTERCARD');

insert into CUSTTRAN
(PID, TranDate, Cust_ID, Tran_Type, Tran_Amt, Pmt_Type)
values(default, '2013-07-21', 2, 'C', 1111.11, 'MASTERCARD');


Select CUST.CUST_ID, Max(TranDate) as TRANDT
From CUST, CUSTTRAN
Where CUST.CUST_ID = CUSTTRAN.CUST_ID and Tran_Type = 'A'
group by CUST.CUST_ID;


Select CUST.CUST_ID, CUSTTRAN.TranDate, CUST.Cust_Type, CUSTTRAN.Tran_Amt, CUSTTRAN.Pmt_Type, RRN(CUSTTRAN)
From
(select CUST.CUST_ID, Max(TranDate) as TRANDT
from CUST, CUSTTRAN
where CUST.CUST_ID = CUSTTRAN.CUST_ID and Tran_Type = 'A'
group by CUST.CUST_ID
) BMAX
join CUSTTRAN on BMAX.CUST_ID = CUSTTRAN.CUST_ID and BMAX.TRANDT = CUSTTRAN.TranDate
join CUST on BMAX.CUST_ID = CUST.CUST_ID
Order by CUST.CUST_ID, CUSTTRAN.TranDate DESC, RRN(CUSTTRAN) DESC
;


SELECT CUST.CUST_ID, CUSTTRAN.TranDate, CUST.Cust_Type, CUSTTRAN.Tran_Amt, CUSTTRAN.Pmt_Type, RRN(CUSTTRAN)
FROM CUST, CUSTTRAN
WHERE CUST.CUST_ID = CUSTTRAN.CUST_ID and Tran_Type = 'A'
ORDER BY CUST.CUST_ID, CUSTTRAN.TranDate DESC, RRN(CUSTTRAN) DESC
FETCH FIRST 1 ROW ONLY;


The first SELECT query returns the correct data, however, I need more columns from the 2 tables:

1 2013-05-01
2 2012-11-12


The second SELECT query returns every Cust_ID, however, multiple rows per Customer:

1 2013-05-01 Residential 3225.03 DISCOVER 3
1 2013-05-01 Residential 2547.52 DISCOVER 2
2 2012-11-12 Commercial 423.22 Ck# 3479 5
2 2012-11-12 Commercial 524.00 Ck# 3478 4


The third SELECT query returns the correct data, but only for the first customer:

1 2013-05-01 Residential 3225.03 DISCOVER 3



I need a SQL statement that will return the LAST Transaction Row for each Customer as follows:

1 2013-05-01 Residential 3225.03 DISCOVER 3
2 2012-11-12 Commercial 423.22 Ck# 3479 5


Thank you in advance for your help!

[TedHolt]

Re: SQL SELECT to retrieve last transaction Row per Customer

with last (Cust_ID, PID) as 
   (select cust_id, max(pid) 
        from custtran
    group by cust_id)

select tran.*, cust.cust_name
  from last
  join custtran as tran
    on last.cust_id = tran.cust_id
   and last.pid = tran.pid
  left join cust
    on last.cust_id = cust.cust_id

[lpowell7]

Re: SQL SELECT to retrieve last transaction Row per Customer

with last (Cust_ID, PID) as 
   (select cust_id, max(pid) 
        from custtran
    group by cust_id)

select tran.*, cust.cust_name
  from last
  join custtran as tran
    on last.cust_id = tran.cust_id
   and last.pid = tran.pid
  left join cust
    on last.cust_id = cust.cust_id

Works perfectly! Thank Ted!