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
Jon Rosenbaum
Devcon Drummer