Hi Jon,

I'm not sure if following query is more efficient but it' more generic.

SELECT * FROM A ;
	JOIN B ON A.Cusip=B.Cusip
	WHERE EXIST ( SELECT * FROM 
		(SELECT Cusip, MAX(Price_Date) as Max_Price_Date FROM B GROUP BY Cusip) dtb
			WHERE dtb.Cusip = B.Cusip and dtb.Max_Price_Date = B.Price_Date)
-- or
SELECT * FROM A ;
	JOIN B ON A.Cusip=B.Cusip
	JOIN (SELECT Cusip, MAX(Price_Date) as Max_Price_Date FROM B GROUP BY Cusip) dtb
			ON dtb.Cusip = B.Cusip and dtb.Max_Price_Date = B.Price_Date)

>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