By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,304 Members | 3,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,304 IT Pros & Developers. It's quick & easy.

Selecting the last date from mutiple duplicate records

P: 4
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:

Expand|Select|Wrap|Line Numbers
  1.  SELECT                  A.PART_NO,
  2.          OUTLET_NUM,
  3.         TXN_DATE
  4.  FROM   TRANSACTIONS  A,
  5.  
  6.  (SELECT MAX(TXN_DATE) AS MAXDATE, PART_NO
  7.  FROM TRANSACTIONS
  8.  GROUP BY PART_NO) MAXRESULTS 
  9.  WHERE A.PART_NO = MAXRESULTS.PART_NO
  10.  AND A.TXN_DATE = MAXRESULTS.MAXDATE
  11.  
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
Sep 5 '07 #1
Share this Question
Share on Google+
2 Replies


debasisdas
Expert 5K+
P: 8,127
Try to create indexes opn the joined fields.

For more details please post all your involved tables structures.
Sep 5 '07 #2

amitpatel66
Expert 100+
P: 2,367
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:

Expand|Select|Wrap|Line Numbers
  1.  SELECT                  A.PART_NO,
  2.          OUTLET_NUM,
  3.         TXN_DATE
  4.  FROM   TRANSACTIONS  A,
  5.  
  6.  (SELECT MAX(TXN_DATE) AS MAXDATE, PART_NO
  7.  FROM TRANSACTIONS
  8.  GROUP BY PART_NO) MAXRESULTS 
  9.  WHERE A.PART_NO = MAXRESULTS.PART_NO
  10.  AND A.TXN_DATE = MAXRESULTS.MAXDATE
  11.  
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.
Sep 5 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.