Any PL/SQL Wizards out there?

[#1]

I have a huge table, which contains a record for every transaction which has an effect on our inventory (yup - BIG table!)
For a given transaction type 'CHC' (CHange Costs) I want to return the Part code, Transaction Date and Transaction cost of the LAST TWO changes.
Because its to be used for tracking updates to the cost of materials, and further for calculating the ongoing effect of these, I just need the two for now.
So,
Table is I50F
Pertinent columns are;

I50PART - Part Code
I50TDAT - Transaction Date
I50UCOST - Price changed to
I50TRNS - Transaction Type - we just want CHC

Sample Data (Including just columns we are interested in)

I50PART              I50TDAT             I50UCOST         I50TRNS 
 
BACCA001             08/03/2006 07:34:51 0.08829          CHC     
BACCA001             25/07/2007 08:26:30 0.10329          CHC     
BACCA001             10/04/2008 16:29:02 0.10639          CHC     
BACCA003             20/06/2006 12:22:30 0.16814          CHC     
BACCA003             25/07/2007 08:26:54 0.17024          CHC     
BACCA003             10/04/2008 13:30:12 0.17535          CHC     
BACCA004             28/08/2007 15:46:03 0.06486          CHC     
BACCA004             28/08/2007 15:49:15 0.06328          CHC     
BACCA004             30/10/2008 09:22:40 0.06952          CHC     
BACCA004             13/01/2009 09:09:07 0.06867          CHC     
BACCA005             25/07/2007 08:27:24 0.06715          CHC     
BACCA005             10/04/2008 15:45:14 0.06916          CHC     
BACCA005             30/10/2008 09:05:17 0.07453          CHC     
BACCA005             13/01/2009 09:06:49 0.07275          CHC

To take a part in isolation, BACCA005;
I'm interested in the last two records.
It makes sense for there to be two records output per part at this stage, as it may be that the powers that be decide that they want the last 3, or 4, or whatever (I'm sure everybody has similar experiences with beancouters)

Is it A) Easy, and B) relatively efficient. There are 2.4m records in the table.

[#2]

hmmm..
a rough first stab

    CURSOR C1 IS
SELECT DISTINCT I50PART FROM  150F;
BEGIN 
FOR PARTREC IN C1
SELECT
I50PART,I50TDAT,
I50UCOST,I50TRNS
FROM
   (SELECT I50PART,I50TDAT,
I50UCOST,I50TRNS
      FROM I50F
    ORDER BY I50TDAT DESC)
    WHERE
   I50PART = C1.150PART
   and I50TRNS = 'CHC'
   and ROWNUM < 2
END LOOP;
END

[#3]

SELECT I50PART, I50TDAT, I50UCOST, I50TRNS
FROM   (SELECT I50PART, I50TDAT, I50UCOST, I50TRNS, row_number() over (PARTITION BY I50PART ORDER BY I50TDAT DESC) rn
        FROM   I50F
        WHERE  I50TRNS = 'CHC')
WHERE  rn <= 2
ORDER BY I50PART, I50TDAT DESC;

This is what was suggested to me. - Looks pretty similar!

[#4]

I'm a SQL Server person, and as a (very) rough stab, without actually trying this out, I would say:

SELECT TOP 2 I50PART, I50TDAT, I50UCOST FROM I50F
WHERE I50TRNS = 'CHC'
ORDER BY I50TDAT DESC

EDIT: WHERE has to come before ORDER BY

[#5]

If you use Order By in oracle in renders the top N bit useless as it will simply extract the top 10 records in the table then order it instead of ordering them then select the top 10.

[#6]

Ah - I see .. told you I'm very used to SQL Server
Sure that works in SQL Sever .. hang on .. yep it does. I have a similar table here and I've just tried a:

SELECT     TOP 2 *
FROM         tblMessage
ORDER BY DateCreated DESC

Which seems to work. I guess for others which TOP before the ORDER BY then you'd need to TOP from a dervied table, which the previous queries are doing.

[#7]

yeah it's one of the few things I prefer about SQL server..
@james in terms of efficiency I'd guess an explain plan on each one is probably best to answer that question. Though mine night need some tweaking