title: How to merge different rows in SQL
body: <p>I have a table with <em>playid</em>, <em>userid</em>, <em>created</em>, and <em>stopped</em>. I want to merge certain rows by keeping the earliest <em>created</em> and the latest <em>stopped</em> within 120 minutes from the earliest <em>created</em>.</p>
  <p>See the following table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>playid</th>
  <th>userid</th>
  <th>created</th>
  <th>stopped</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>a01</td>
  <td>2021-01-30 02:29:58.559858</td>
  <td>2021-01-30 02:40:56.558436</td>
  </tr>
  <tr>
  <td>2</td>
  <td>a01</td>
  <td>2021-01-30 02:41:24.023358</td>
  <td>2021-01-30 02:55:24.112713</td>
  </tr>
  <tr>
  <td>3</td>
  <td>a01</td>
  <td>2021-01-30 02:57:30.178579</td>
  <td>2021-01-30 03:11:14.866678</td>
  </tr>
  <tr>
  <td>4</td>
  <td>a01</td>
  <td>2021-01-30 03:11:41.098424</td>
  <td>2021-01-30 03:22:50.155918</td>
  </tr>
  <tr>
  <td>5</td>
  <td>a01</td>
  <td>2021-01-30 03:23:20.545288</td>
  <td>2021-01-30 03:36:37.027486</td>
  </tr>
  <tr>
  <td>6</td>
  <td>a01</td>
  <td>2021-01-30 03:46:10.237971</td>
  <td>2021-01-30 03:59:17.526151</td>
  </tr>
  <tr>
  <td>7</td>
  <td>a01</td>
  <td>2021-01-30 03:59:57.020326</td>
  <td>2021-01-30 04:16:25.948311</td>
  </tr>
  <tr>
  <td>8</td>
  <td>a01</td>
  <td>2021-01-30 04:26:28.968568</td>
  <td>2021-01-30 04:30:11.863079</td>
  </tr>
  <tr>
  <td>9</td>
  <td>a01</td>
  <td>2021-01-30 04:30:14.43786</td>
  <td>2021-01-30 04:41:42.231915</td>
  </tr>
  <tr>
  <td>10</td>
  <td>a01</td>
  <td>2021-01-30 04:43:17.669945</td>
  <td>2021-01-30 04:45:10.443101</td>
  </tr>
  <tr>
  <td>11</td>
  <td>a01</td>
  <td>2021-01-30 04:45:14.50346</td>
  <td>2021-01-30 04:47:07.082114</td>
  </tr>
  <tr>
  <td>12</td>
  <td>a01</td>
  <td>2021-01-30 04:47:10.334574</td>
  <td>2021-01-30 04:49:30.96017</td>
  </tr>
  <tr>
  <td>13</td>
  <td>a01</td>
  <td>2021-01-30 04:49:34.146011</td>
  <td>2021-01-30 04:50:54.45988</td>
  </tr>
  <tr>
  <td>14</td>
  <td>a01</td>
  <td>2021-01-30 04:50:57.948305</td>
  <td>2021-01-30 05:11:01.246284</td>
  </tr>
  <tr>
  <td>15</td>
  <td>a01</td>
  <td>2021-01-30 05:39:29.387396</td>
  <td>2021-01-30 05:41:39.508654</td>
  </tr>
  <tr>
  <td>16</td>
  <td>a01</td>
  <td>2021-01-30 05:41:44.524951</td>
  <td>2021-01-30 05:43:38.231266</td>
  </tr>
  <tr>
  <td>17</td>
  <td>a01</td>
  <td>2021-01-30 05:43:40.785809</td>
  <td>2021-01-30 05:54:40.711381</td>
  </tr>
  <tr>
  <td>18</td>
  <td>a01</td>
  <td>2021-01-30 05:55:10.851725</td>
  <td>2021-01-30 05:58:24.262351</td>
  </tr>
  <tr>
  <td>19</td>
  <td>a01</td>
  <td>2021-01-30 05:58:29.43821</td>
  <td>2021-01-30 06:00:50.870644</td>
  </tr>
  <tr>
  <td>20</td>
  <td>a01</td>
  <td>2021-01-30 06:00:54.168696</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>21</td>
  <td>a01</td>
  <td>2021-01-30 14:41:01.529666</td>
  <td>2021-01-30 14:53:01.349479</td>
  </tr>
  <tr>
  <td>22</td>
  <td>a01</td>
  <td>2021-01-30 14:53:48.053136</td>
  <td>2021-01-30 15:05:09.532786</td>
  </tr>
  <tr>
  <td>23</td>
  <td>a01</td>
  <td>2021-01-31 15:11:08.547111</td>
  <td>2021-01-31 15:11:37.564802</td>
  </tr>
  <tr>
  <td>24</td>
  <td>a01</td>
  <td>2021-01-31 15:11:40.248499</td>
  <td>2021-01-31 15:13:40.21787</td>
  </tr>
  <tr>
  <td>25</td>
  <td>a01</td>
  <td>2021-01-31 15:13:59.373145</td>
  <td>2021-01-31 15:31:54.099898</td>
  </tr>
  <tr>
  <td>26</td>
  <td>a01</td>
  <td>2021-01-31 15:32:23.20448</td>
  <td>2021-01-31 15:46:33.993751</td>
  </tr>
  <tr>
  <td>27</td>
  <td>a01</td>
  <td>2021-01-31 16:55:19.141051</td>
  <td>2021-01-31 17:03:43.464444</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want the query to give this table (see below). Please note that the next <em>created</em> is after the previous <em>stopped</em>, meaning the <em>created</em> with <em>playid</em> &quot;8&quot; is after <em>stopped</em> with <em>playid</em> &quot;1&quot;:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>playid</th>
  <th>userid</th>
  <th>created</th>
  <th>stopped</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>a01</td>
  <td>2021-01-30 02:29:58.559858</td>
  <td>2021-01-30 04:16:25.948311</td>
  </tr>
  <tr>
  <td>8</td>
  <td>a01</td>
  <td>2021-01-30 04:26:28.968568</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>21</td>
  <td>a01</td>
  <td>2021-01-30 14:41:01.529666</td>
  <td>2021-01-30 15:05:09.532786</td>
  </tr>
  <tr>
  <td>23</td>
  <td>a01</td>
  <td>2021-01-31 15:11:08.547111</td>
  <td>2021-01-31 17:03:43.464444</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Here is the query I have tried:</p>
  <pre><code>SELECT
      MIN(t1.playid) as playid,
      t1.userid,
      SAFE_CAST(t1.created AS DATETIME) as created,
      MAX(SAFE_CAST(t2.stopped AS DATETIME)) as stopped
  FROM
      MyTable as t1
  LEFT OUTER JOIN
      MyTable as t2
          ON t1.userid = t2.userid
  WHERE
      DATETIME_DIFF(SAFE_CAST(t2.stopped AS DATETIME),SAFE_CAST(t1.created AS DATETIME),MINUTE) 
      &lt; 120
  GROUP BY
      t1.playid, t1.userid, t1.created
  </code></pre>
  <p>and querying the previous table using this query:</p>
  <pre><code>SELECT
      MIN(playid) AS playid,
      userid,
      MIN(created) AS created,
      stopped,
  FROM
      MyTable2
  GROUP BY
      userid
      stopped
  </code></pre>
  <p>This is the (wrong) table I get when I run the first query:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>playid</th>
  <th>userid</th>
  <th>created</th>
  <th>stopped</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>a01</td>
  <td>2021-01-30 02:29:58.559858</td>
  <td>2021-01-30 04:16:25.948311</td>
  </tr>
  <tr>
  <td>2</td>
  <td>a01</td>
  <td>2021-01-30 02:41:24.023358</td>
  <td>2021-01-30 04:30:11.863079</td>
  </tr>
  <tr>
  <td>3</td>
  <td>a01</td>
  <td>2021-01-30 02:57:30.178579</td>
  <td>2021-01-30 04:50:54.45988</td>
  </tr>
  <tr>
  <td>4</td>
  <td>a01</td>
  <td>2021-01-30 03:11:41.098424</td>
  <td>2021-01-30 05:11:01.246284</td>
  </tr>
  <tr>
  <td>5</td>
  <td>a01</td>
  <td>2021-01-30 03:23:20.545288</td>
  <td>2021-01-30 05:11:01.246284</td>
  </tr>
  <tr>
  <td>6</td>
  <td>a01</td>
  <td>2021-01-30 03:46:10.237971</td>
  <td>2021-01-30 05:43:38.231266</td>
  </tr>
  <tr>
  <td>7</td>
  <td>a01</td>
  <td>2021-01-30 03:59:57.020326</td>
  <td>2021-01-30 05:58:24.262351</td>
  </tr>
  <tr>
  <td>8</td>
  <td>a01</td>
  <td>2021-01-30 04:26:28.968568</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>9</td>
  <td>a01</td>
  <td>2021-01-30 04:30:14.43786</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>10</td>
  <td>a01</td>
  <td>2021-01-30 04:43:17.669945</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>11</td>
  <td>a01</td>
  <td>2021-01-30 04:45:14.50346</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>12</td>
  <td>a01</td>
  <td>2021-01-30 04:47:10.334574</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>13</td>
  <td>a01</td>
  <td>2021-01-30 04:49:34.146011</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>14</td>
  <td>a01</td>
  <td>2021-01-30 04:50:57.948305</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>15</td>
  <td>a01</td>
  <td>2021-01-30 05:39:29.387396</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>16</td>
  <td>a01</td>
  <td>2021-01-30 05:41:44.524951</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>17</td>
  <td>a01</td>
  <td>2021-01-30 05:43:40.785809</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>18</td>
  <td>a01</td>
  <td>2021-01-30 05:55:10.851725</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>19</td>
  <td>a01</td>
  <td>2021-01-30 05:58:29.43821</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>20</td>
  <td>a01</td>
  <td>2021-01-30 06:00:54.168696</td>
  <td>2021-01-30 06:12:37.210234</td>
  </tr>
  <tr>
  <td>21</td>
  <td>a01</td>
  <td>2021-01-30 14:41:01.529666</td>
  <td>2021-01-30 15:05:09.532786</td>
  </tr>
  <tr>
  <td>22</td>
  <td>a01</td>
  <td>2021-01-30 14:53:48.053136</td>
  <td>2021-01-30 15:05:09.532786</td>
  </tr>
  <tr>
  <td>23</td>
  <td>a01</td>
  <td>2021-01-31 15:11:08.547111</td>
  <td>2021-01-31 17:03:43.464444</td>
  </tr>
  <tr>
  <td>24</td>
  <td>a01</td>
  <td>2021-01-31 15:11:40.248499</td>
  <td>2021-01-31 17:03:43.464444</td>
  </tr>
  <tr>
  <td>25</td>
  <td>a01</td>
  <td>2021-01-31 15:13:59.373145</td>
  <td>2021-01-31 17:03:43.464444</td>
  </tr>
  <tr>
  <td>26</td>
  <td>a01</td>
  <td>2021-01-31 15:32:23.20448</td>
  <td>2021-01-31 17:03:43.464444</td>
  </tr>
  <tr>
  <td>27</td>
  <td>a01</td>
  <td>2021-01-31 16:55:19.141051</td>
  <td>2021-01-31 17:03:43.464444</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The problem here is that the column <em>created</em> in the next row does not jump to the next <em>playid</em> with a <em>created</em> value which is larger/later than the <em>stopped</em> value of the current <em>playid</em>. Instead it just iterates through the table. Please see the tables above and use the <em>playid</em> column to make it easier to understand.</p>
  <p>PS: I am very new to SQL. Please help me optimize this if this is very ineffective. Would be best to only have one query, of course.</p>
  <p>Thank you!</p>