SQL HYDT | Get records for just one earlier date anytime back in Time

In Oracle: Use the dense Rank function to achieve this:
Query Suppose we need to get Records for Date : 06-SEP-2011 (and not any earlier)
when we input Argument Date (say) : 08-SEP-2011
MASTER_JOB_ID JOB_ID VALUE_DT
123 135 01-SEP-11
123 324 01-SEP-11
123 325 01-SEP-11
123 134 01-SEP-11
345 8964 06-SEP-11
345 8965 06-SEP-11
678 12828 08-SEP-11
678 12829 08-SEP-11
One way to achieve is to use denserank oracle function:

SELECT
MASTER_JOB_ID, JOB_ID, VALUE_DT
FROM
(
SELECT
MASTER_JOB_ID, JOB_ID, VALUE_DT AS VALUE_DT,
dense_rank() over (order by VALUE_DT DESC) AS denserank
FROM
TABLE_IN_QUESTION
WHERE
MASTER_JOB_ID = ‘123’
AND VALUE_DT <= TO_DATE('08-09-2011','DD-MM-YYYY')
)
WHERE
denserank = 2;

#sql-oracle-denserank