Top N results

[joconnor]

I'm trying to get to last four prices we've payed for a sub-set of parts. I'm able to get the last four prices we've payed for a particular part but I want to exted it to all parts. the current query I have is:

SELECT HIST.*, ROWNUM
  FROM (SELECT APD.Part_Number,
                           APD.Actual_Unit_Price
                FROM AP_DETAIL APD
             WHERE APD.Part_Number = 'XXXXXXXXXX'
             ORDER BY APD.Entered_Date DESC) HIST
 WHERE ROWNUM <= 4

I just need to get rid of the where clause in the inline view and I would have the result I need. Is there any way to do this?

[amitpatel66]

I'm trying to get to last four prices we've payed for a sub-set of parts. I'm able to get the last four prices we've payed for a particular part but I want to exted it to all parts. the current query I have is:

SELECT HIST.*, ROWNUM
  FROM (SELECT APD.Part_Number,
                           APD.Actual_Unit_Price
                FROM AP_DETAIL APD
             WHERE APD.Part_Number = 'XXXXXXXXXX'
             ORDER BY APD.Entered_Date DESC) HIST
 WHERE ROWNUM <= 4

I just need to get rid of the where clause in the inline view and I would have the result I need. Is there any way to do this?

 
SELECT HIST.*
  FROM (SELECT APD.Part_Number,row_number() OVER(PARTITION BY part_number ORDER BY part_number) rnk
                           APD.Actual_Unit_Price 
                FROM AP_DETAIL APD
             --WHERE APD.Part_Number = 'XXXXXXXXXX'      ) HIST
 WHERE rnk <= 4
ORDER BY part_number,rnk