I have the below records in a table:
I have to get the unit_retail from the last action_datei.e 06-APR-13.
My result should be 1.99 . I have to get the unit_retail from the date just before the last date.
Here's one way to do that:
WITH got_r_num AS
SELECT x.* -- or list columns wanted
, ROW_NUMBER () OVER ( ORDER BY action_date DESC NULLS LAST) AS r_num
SELECT * -- or list all columns except r_num
WHERE r_num = 2
If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test this.
Starting in Oracle 11.2, the NTH_VALUE function might help you: