title: SQL Query: last, second last, third last..value (Microsoft SQL)
body: <p>I am trying to build reports for sport team. The results are stored in a SQL-Database like this:
  <img src="https://i.stack.imgur.com/dIWPj.jpg" alt="Table"></p>
  
  <p>For the reports I need the columns Home2-Away4 to be filled with the corresponding results.
  Which means: </p>
  
  <p>Imagine: ID=10 and Home(no.) (Team#) = 1 --> <strong>Home1</strong> = result <strong>one</strong> game before which is ID=3 and the team played away and the result was 0 (=home1). This is what I already have done, but <strong>Home2</strong> = result <strong>two</strong> games ago, as <strong>Home3</strong>=result <strong>three</strong> games ago and so on.</p>
  
  <p>And here is how I have done this:</p>
  
  <pre><code>SELECT top 1 [result_home] from (SELECT Top 2 [result_home] FROM 
  (SELECT top 2 [result_home]  from [Table]
  where [ID] &lt; 10  and ( [Home(no.)]=1 or [Away(No.)]=1 ) order by [ID] desc  ) top2     )top1 
  </code></pre>
  
  <p>(Microsoft SQL Server Express 2012 btw.)</p>
  
  <p>I have written a small Java code which decides if to pick from result_home or result_away.</p>
  
  <p>The basic question is: How is it possible to query for game results two and more gamedays ago?</p>
  
  <p>Thanks in advance.</p>
  
  <p>Kind regards</p>
  
  <p>Stefan</p>