oracle - Ranking / Finding Dates -

i have these records:

phot    1/16/2017   20170118    0.01 phot    1/16/2017   20170117    0.0103 phot    1/16/2017   20170116    0.0101 phot    1/16/2017   20170115    0.0101 phot    1/16/2017   20170114    0.0105 . . . msrt    12/31/2016  20170327    0.855 msrt    12/31/2016  20170324    0.87 msrt    12/31/2016  20170323    0.87 msrt    12/31/2016  20170322    0.88 msrt    12/31/2016  20170321    0.89 . . . 

basically need record closest date in column 3 <= date in column 2.

it should partitioned column 1.i need 1 record, being 1 has closed column 2 date column 1 date.

can done dense_rank? or should using row_number?

the following query should help:

 select * (  select row_number() on (partition t.col1 order        abs(t.col2-to_date(t.col3, 'yyyymmdd'))) rw, t.*  tbl t t.col2 >= to_date(t.col3, 'yyyymmdd')) rw = 1; 

explanation:

the inner query use to_date function on col3 format date same pattern col2.

then can compare col2 in clause. next, rank absolute space between 2 columns (again to_date) each col1 (partition col1, order abs).

finally, take top row each col1 group (rw=1).

note if using dense_ranke, can create several rows each col1, because if have 2 rows same absolute value - dense_ranke rank them same number (let's -1), , can few rows each column. row_number give unique rank each row.