Oracle: get the closest date for each id efficiently

I'm trying to create a query that returns the closest (above) price for each id from table price for every date in table date.

date has these dates in it:

date
2010-11-25
2010-11-24
2010-11-10

price is as follows:

id  date        price
A   2010-11-26  24.99
A   2010-11-24  27.99
A   2010-11-13  22.12
B   2010-11-26  26.51
B   2010-11-24  23.24
B   2010-11-22  27.95

So for 2010-11-25 I should get

id  date        price
A   2010-11-26  24.99
B   2010-11-26  26.51

for 2010-11-10

id  date        price
A   2010-11-13  22.12
B   2010-11-22  27.95

for 2010-11-24

id  date        price
A   2010-11-24  27.99
B   2010-11-24  23.24

etc.

I believe getting the result for a given date is doable (maybe group by...having), however I am lo开发者_StackOverflow中文版oking for a solution that does it for all the dates.

EDIT:

There was an error in the example, corrected...

---

I think you mean this, but there may be an error in your example. (Or misunderstand it).

select
  id, date,price
from
  (select
    p.id,
    p.date,
    p.price,
    dense_rank() over (partition by d.date, p.id order by p.date) as rank
  from
    date d
    inner join price p on p.date > d.date)
where
  rank = 1

---

This should work for you, I think:

select x.date_1 as candidate_date ,
       t.*
from ( select d."date"        as date_1  ,
              p.id ,
              min( p."date" ) as date_2
        from      "date" d
        left join price  p on p."date" >= d."date"
        group by p.id     ,
                 d."date"
     ) x
left join price t on t.id     = x.id
                 and t."date" = x.date_2
order by 1,2,3

The virtual table in the from clause should give you 1 row for every "id" in the price table with a date greater than or equal to your candidate date from the date table.

---

SELECT
    d.date AS searchDate
    p.id
    p.date
    p.price
FROM 
    date d
  ,                              --- this is a CROSS JOIN
    ( SELECT DISTINCT id
      FROM price
    ) product
  JOIN
    price p
      ON  p.id = product.id
      AND p.date =
          ( SELECT MIN(p2.date)
            FROM price AS p2
            WHERE p2.date >= d.date
          )