Selecting the last date from mutiple duplicate records

[n4nz]

I have a table in oracle that contains transaction data by item part number.
This means for each part number there will be more than one transaction date, what i am trying to extract is the last transaction date for each part number in a driving table.

so..
I have created a table that has 1 column named PART_NO, with numerous part numbers contained in it, i want to left join this to my transaction table and return the last transaction for those part numbers. The SQL so far is as follows:

 SELECT                  A.PART_NO,
         OUTLET_NUM,
        TXN_DATE
 FROM   TRANSACTIONS  A,
 
 (SELECT MAX(TXN_DATE) AS MAXDATE, PART_NO
 FROM TRANSACTIONS
 GROUP BY PART_NO) MAXRESULTS 
 WHERE A.PART_NO = MAXRESULTS.PART_NO
 AND A.TXN_DATE = MAXRESULTS.MAXDATE
 

This will return the last transaction for each part number in the transaction table, but i need to limit this to those records in my driving table as the statement takes an age to run. any ideas? Thanks in advance

[debasisdas]

Try to create indexes opn the joined fields.

For more details please post all your involved tables structures.

[amitpatel66]

I have a table in oracle that contains transaction data by item part number.
This means for each part number there will be more than one transaction date, what i am trying to extract is the last transaction date for each part number in a driving table.

so..
I have created a table that has 1 column named PART_NO, with numerous part numbers contained in it, i want to left join this to my transaction table and return the last transaction for those part numbers. The SQL so far is as follows:

 SELECT                  A.PART_NO,
         OUTLET_NUM,
        TXN_DATE
 FROM   TRANSACTIONS  A,
 
 (SELECT MAX(TXN_DATE) AS MAXDATE, PART_NO
 FROM TRANSACTIONS
 GROUP BY PART_NO) MAXRESULTS 
 WHERE A.PART_NO = MAXRESULTS.PART_NO
 AND A.TXN_DATE = MAXRESULTS.MAXDATE
 

This will return the last transaction for each part number in the transaction table, but i need to limit this to those records in my driving table as the statement takes an age to run. any ideas? Thanks in advance

From the above Query, I see u have used the same table even in INLINE View. So I have re-structured the Query as shown below:

SELECT t.part_no,t.outlet_num, t.txn_date FROM TRANSACTIONS t WHERE
t.txn_date = (SELECT max(txn_date) from transactions where txn_date = t.txn_date and part_no = t.part_no group by part_no )

Try executing the above Query. Please do post back.