title: Aggregate function in comparison of 2 rows in the same table (SQL)
body: <p>Given the table definition:</p>
  
  <pre><code>create table mytable (
      id integer,
      mydate datetime,
      myvalue integer )
  </code></pre>
  
  <p>I want to get the following answer by a single SQL query:</p>
  
  <pre><code>id date_actual value_actual date_previous value_previous
  </code></pre>
  
  <p>where:</p>
  
  <pre><code>date_previous is the maximum of all the dates preceeding date_actual 
  for each id and values correspond with the two dates 
  {max(date_previous) &lt; date_actual ?}
  </code></pre>
  
  <p>How can I achieve that?</p>
  
  <p>Thanks for your hints</p>