Select i.investment_name, i.investment_type, v.valuation_date, v.valuation_amount
from tbl_investment i
inner join tbl_valuation v on i.investment_id=v.investment_id
where v.valuation_date=(select max(t.valuation_date)
from tbl_valuation t where t.investment_id = i.investment_id)

that sort of thing?

(edit, put the join in)

[Edited by Fosters - 4/3/2003 12:32:57 PM]

 

I have an sql problem that I can't figure out. It relates to investments and valuations of those investments at certain dates.


tbl_investment has details of the investment:
---------
investment_id
investment_name
investment_type

tbl_valuation holds the valuations and the dates they took place:
---------
valuation_id
investment_id
valuation_date
valuation_amount


An investment can have many valuation_amounts.

How can I run a select query that returns all the data from tbl_investment but only the most recent valuation_amount from tbl_valuation?

Doing a simple join returns all the valuations, Select Top 1 returns only the first row from tbl_prod and I everything I've tried with select(min) or select(distinct) either doesn't give what I want or is bad syntax.

I need to run this query to return the complete list of investments in tbl_investment, not just the first one, or a specific one.

Thanks!

 

Exactly what I was after thanks!