find the most recent update row

Hi,
I have the following table.
Object_ID Rating Effective_Date
A 1 5/15/03
A 2 6/30/03
A 1 7/15/03
B 2 5/15/03
B 4 7/20/03
Another table is
Object_ID quantity query_date
A 1000 7/12/03
B 2000 7/12/03(must be the same for
all row in the column query_date)
and I wish to return the lastest Rating like
Object_ID quantity rating effective date
A 1000 2 6/30/03
B 2000 2 5/15/03
Could anyone have a one line T-sql to perform this and
advise?
I have tried to use a min function to compare the query
date and effective and choose the min non-negative number.
But it seems work fine for single row record, but if
multiple record, it seems difficult to do the grouping.
Thx..(untested)
SELECT t2.Object_ID, t2.quantity, t1.rating, t1.Effective_Date
FROM table1 t1
INNER JOIN table2 t2
ON t1.Object_ID = t2.Object_ID
WHERE t2.Effective_Date = (SELECT MAX(Effective_Date) FROM table2 t3
WHERE t3.Effective_Date < t1.query_date
AND t3.Object_ID = t2.Object_ID)
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"AW" <alexwong97@.hotmail.com> wrote in message
news:077b01c34497$dffc24e0$a001280a@.phx.gbl...
> Hi,
> I have the following table.
> Object_ID Rating Effective_Date
> A 1 5/15/03
> A 2 6/30/03
> A 1 7/15/03
> B 2 5/15/03
> B 4 7/20/03
> Another table is
> Object_ID quantity query_date
> A 1000 7/12/03
> B 2000 7/12/03(must be the same for
> all row in the column query_date)
> and I wish to return the lastest Rating like
> Object_ID quantity rating effective date
> A 1000 2 6/30/03
> B 2000 2 5/15/03
>
> Could anyone have a one line T-sql to perform this and
> advise?
> I have tried to use a min function to compare the query
> date and effective and choose the min non-negative number.
> But it seems work fine for single row record, but if
> multiple record, it seems difficult to do the grouping.
> Thx..sql