For each value in one table, select nearest value in another table

[d000hg]

This is pretty simple to describe:
Table A & B both have a date field. I need a query/procedure which will do:
SELECT A.date, { max(B.date) where B.date < A.date}
Example:

A.date.... B.date 
======.... ====== 
10/10/2006 01/01/1999 
01/04/2007 12/12/2006 

Result: A.date | Max B.date < A.date 
======..=================== 
10/10/2006 01/01/1999 // the biggest date in B which is < than 10/10/2006 
01/04/2007 12/12/2006

So each row contains the date of an A, and the biggest date in B which is less than this value. It seems like it should be so simple but I just can't get it to work in a query, and I don't know the syntax Sybase use for writing procedures - if I were in Oracle I'd have given up and written a FOR loop by now... but a query would be neater anyway.

Many thanks for any help.

 

[Hans63]

Hi,

your question lacks a little information. Do you have any other join conditions between tables A and B?
What RDBMS are you using (Oracle, DB2, SQLserver, etc)?

But let's give it a try (this is based on Oracle):
select a.date
, max(case
when a.date > b.date then b.date
else null
end)
from table1 a
, table2 b
where join_condition
group by a.date