I've created a query that does indeed work the way I want, but I'm not convinced it's the most efficient way to achieve the results. Here's the situation:

Table A has a one-to-many relationship into Table B using a "cusip" column. Table B has a "price_date" column, thereby having multiple prices for the same cusip. The result set I need is every record from table A and only the most recent price, price_date, and value in Table B.

Here's the query I wrote:

SELECT A.*, 
   (SELECT TOP 1 Price
    FROM B
    WHERE A.Cusip=B.Cusip
    ORDER BY Price_Date DESC),
   (SELECT TOP 1 Price_Date
    FROM B
    WHERE A.Cusip=B.Cusip
    ORDER BY Price_Date DESC),
   (SELECT TOP 1 Value
    FROM B
    WHERE A.Cusip=B.Cusip
    ORDER BY Price_Date DESC)
FROM A

Is there a more efficient way to get the multiple columns from the topmost record in table B or is my way pretty much it?

Thanks.

Jon