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! :D
|
All times are GMT +1. The time now is 04:47 PM. |
© 2024 MH Sub I, LLC dba Internet Brands