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?

[code=oracle]

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

[/code]