title: How to self-join table in a way that every record is joined with the "previous" record?
body: <p>I have a MS SQL table that contains stock data with the following columns: <code>Id, Symbol, Date, Open, High, Low, Close</code>.</p>
  
  <p>I would like to self-join the table, so I can get a day-to-day % change for <code>Close</code>.</p>
  
  <p>I must create a query that will join the table with itself in a way that every record contains also the data from the previous session (be aware, that I cannot use yesterday's date).</p>
  
  <p>My idea is to do something like this:</p>
  
  <pre><code>select * from quotes t1
  inner join quotes t2
  on t1.symbol = t2.symbol and
  t2.date = (select max(date) from quotes where symbol = t1.symbol and date &lt; t1.date)
  </code></pre>
  
  <p>However I do not know if that's the correct/fastest way. What should I take into account when thinking about performance? (E.g. will putting UNIQUE index on a (Symbol, Date) pair improve performance?)</p>
  
  <p>There will be around 100,000 new records every year in this table. I am using MS SQL Server 2008</p>