title: SQL join using TOP condition in subquery
body: <p>I'm writing a stock program to improve my programming skills and I've hit a roadblock.</p>
  
  <p>I have two tables I'm working with for:</p>
  
  <pre><code>**stocks**
  ---------
  id
  name
  symbol
  ipo_year
  sector
  industry
  
  **stock_trends**
  ----------------
  stock_id
  trend_id
  direction_id
  date
  price
  breakout_price
  
  **trends**
  ----------
  id
  type
  </code></pre>
  
  <p>An entry is made into the stock_trends table for that stock when a condition of one of my four trends are met.</p>
  
  <p>What I'm looking to do is create a query that will return all the information from the stock table and the date from the stock_trends table where the most recent entry in stock_trends for that stock is the trend_id I'm interested in looking at.</p>
  
  <p>I have this query that works great which returns the most recent trend if for a single stock.</p>
  
  <pre><code>SELECT top 1 stock_id, trend_id, [timestamp], price, breakout_price from stock_trends
  WHERE stock_id = @stock_id and trend_id = @trend_id order by [timestamp] desc
  </code></pre>
  
  <p>I just haven't been able to figure out how to write a query that returns the stocks whose top entry in the stock_trends table is the trend I wish to analyze.</p>
  
  <p>Thanks in advance for your help!</p>
  
  <p><strong>Edit</strong></p>
  
  <p>So I have made some progress and I'm almost there. I'm using this query to return the max "timestamp" (it's really a date, just have to fix it) for each stock.</p>
  
  <pre><code>select s.*, v.latest_trend_date from stocks s
  join(select stock_id, MAX(timestamp) as latest_trend_date from stock_trends st
  group by st.stock_id) v on v.stock_id = s.id
  </code></pre>
  
  <p>Now if I could only find a way to determine which trend_id "latest_trend_date" is associated with I would be all set!</p>