title: How to get consecutive records have same value in SQL
body: <p>Say I have a table like below, how can I get all records have same account_id, ordered by begin_date, and consecutive records have same value</p>
  
  <pre><code>begin_date       value    account_id   other
  20180701    1013        222         11
  20180702    1013        222         11
  20180701    1022        222         12
  20180702    1022        222         12
  20180703    1023        222         12
  20180701    1024        111         13
  20180702    1024        111         13 
  </code></pre>
  
  <p>In this case, following should be returned:</p>
  
  <pre><code>begin_date       value    account_id   other
  20180701    1013        222         11
  20180702    1013        222         11
  20180701    1022        222         12
  20180702    1022        222         12
  20180701    1024        111         13
  20180702    1024        111         13
  </code></pre>
  
  <p>I have tried to use key word "WITH", but looks like <a href="https://stackoverflow.com/questions/46410422/syntax-error-with-is-not-valid-input-in-this-position">it is not valid anymore</a>, this is what I wrote:</p>
  
  <pre><code>WITH Ordered AS (
              SELECT *,  ROW_NUMBER() OVER (ORDER BY account_id, begin_date, value) AS order_id
              FROM addresses)
  SELECT o1.account_id, o1.value
  FROM Ordered o1
      INNER JOIN Ordered o2 ON o2.account_id == o1.account_id AND o2.value = o1.value AND s2.order_id = s1.order_id + 1
  UNION
  SELECT o2.account_id, o2.value 
  FROM Ordered o1
      INNER JOIN Ordered o2 ON o2.account_id == o1.account_id AND o2.value = o1.value AND s2.order_id = s1.order_id + 1
  order by
          1,2;
  </code></pre>