title: Order values in specific way based on values in group before and after
body: <p>I have a task where i need to read a table from a database that looks like following for example:</p>
  <pre><code>------------------------------------
  |   id    |   value   |    date    |
  ------------------------------------
  |   1     |   val1    | 23-12-2020 |
  ------------------------------------
  |   2     |   val2    | 23-12-2020 |
  ------------------------------------
  |   3     |   val1    | 24-12-2020 |
  ------------------------------------
  |   4     |   val2    | 24-12-2020 |
  ------------------------------------
  |   5     |   val3    | 24-12-2020 |
  ------------------------------------
  |   6     |   val1    | 25-12-2020 |
  ------------------------------------
  |   7     |   val3    | 25-12-2020 |
  ------------------------------------
  |   8     |   val2    | 26-12-2020 |
  ------------------------------------
  |   9     |   val3    | 26-12-2020 |
  ------------------------------------
  </code></pre>
  <p>In the table, i could do grouping by the date, which would result in 4 groups (23-12-2020, 24-12-2020, 25-12-2020, 26-12-2020). I need to determine the first and the last element of each of the groups. The last element for date 23-12-2020 directly influences the first element for the group on date 24-12-2020 in some continuous fashion. That for example could result in both:</p>
  <pre><code>------------------------------------  or  ------------------------------------ 
  |   id    |   value   |    date    |      |   id    |   value   |    date    |
  ------------------------------------      ------------------------------------
  |   1     |   val1    | 23-12-2020 |      |   2     |   val2    | 23-12-2020 |
  ------------------------------------      ------------------------------------
  |   2     |   val2    | 23-12-2020 |      |   1     |   val1    | 23-12-2020 |
  ------------------------------------      ------------------------------------
  |   4     |   val2    | 24-12-2020 |      |   3     |   val1    | 24-12-2020 |
  ------------------------------------      ------------------------------------
  </code></pre>
  <p>So for all elements in a group (23-12-2020), i would check if they appear in the next group (24-12-2020). If only of these elements appear there, then that element would be last in the current group (23-12-2020) and first of the next group (24-12-2020). The fact in the example is that both val1 and val2 appear in the group with the date 24-12-2020. In that case i would look at the group with the date 25-12-2020, and if both appear there, then watch next one etc. until we find the group where only one element appears which would determine the real order. If none of them appears, then the order could be any only following the constraints that the last of one group is the first of the next one.</p>
  <p>As for the date 25-12-2020 only val1 appears, that would enforce the following order:</p>
  <pre><code>------------------------------------
  |   id    |   value   |    date    |
  ------------------------------------
  |   1     |   val1    | 23-12-2020 |
  ------------------------------------
  |   2     |   val2    | 23-12-2020 |
  ------------------------------------
  |   3     |   val2    | 24-12-2020 |
  ------------------------------------
  |   4     |   val3    | 24-12-2020 |
  ------------------------------------
  |   5     |   val1    | 24-12-2020 |
  ------------------------------------
  |   6     |   val1    | 25-12-2020 |
  ------------------------------------
  |   7     |   val3    | 25-12-2020 |
  ------------------------------------
  |   8     |   val3    | 26-12-2020 |
  ------------------------------------
  |   9     |   val2    | 26-12-2020 |
  ------------------------------------
  </code></pre>
  <p>For note, the order between the first and last element of the group which contains more than 2 elements is not important.</p>
  <p>I'm not sure how i would do this in an efficient way, or is there really a library that would help with this, or is there a specific data structure that could solve the problem. It's relatively complex, so i did the search (maybe with not the right keywords), but could not find anything useful.</p>
  <p>Maybe it's also solvable with a specific query.</p>
  <p>Thanks in advance.</p>
  <p>PS. I'm not really sure why stackoverflow bolds parts of the code</p>