Closest Date in Oracle

Question

I am trying to get the closest date to a given date in Oracle. I have been working form How to get the closest dates in Oracle sql, but the example in that question uses two different tables. I'm no PL SQL guru and I'm struggling to get this to work. I have a single table that contains an ID field and a Date field. I need the ID that it closest to the date passed into the query.

select *
  from ( select SEQ_ID, ENTERED_DATE, rank() over ( partition by ENTERED_DATE order by difference asc ) as rnk
         from ( select SEQ_ID, ENTERED_DATE, abs(ENTERED_DATE - 2/9/1999) from DOWNTIME_DETAILS)) as difference             
 where rnk = 1

This gives me an error: "SQL command not properly ended"

How can I fix the query? What am I doing wrong?

Answer 1

The as difference is assigning a table alias. You can't use as for table aliases, only for column aliases (so as rnk is OK). Just remove the second as. As you are refering to difference in the outer query, it looks like you meant it to be a column alias and just had it in the wrong place:

select *
from (
  select SEQ_ID, ENTERED_DATE,
    rank() over ( order by difference ) as rnk
  from (
    select SEQ_ID, ENTERED_DATE,
      abs(ENTERED_DATE - to_date('2/9/1999', 'MM/DD/YYYY')) as difference
    from DOWNTIME_DETAILS
  )
)
where rnk = 1

You also had a date without any quote marks, so that would have been interpreted as numbers in this case, and wouldn't have had the effect you were looking for. You should always use explicit conversion; I've guessed your date format. And you should not be partitioning by the original entered_date as that will make everything rank as 1. If you have two records that have the same difference they will still both rank as 1 so you'll see both. You could add a way to break ties by modifying the order by, e.g.

    rank() over ( order by difference , entered_date, seq_id ) as rnk

... but you'll need to specify the criteria so it makes sense for your data and situation.

You could also do this:

select max(SEQ_ID) keep (dense_rank first
    order by abs(ENTERED_DATE - to_date('2/9/1999', 'MM/DD/YYYY')))
    as seq_id,
  max(ENTERED_DATE) keep (dense_rank first
    order by abs(ENTERED_DATE - to_date('2/9/1999', 'MM/DD/YYYY')))
    as entered_date
from DOWNTIME_DETAILS;

... but then you have to supply the date twice.