title: How to write complex Sqlite SELECT statements?
body: <p>So, allow me to give an example. This is how the table looks like:</p>
  <pre><code>+----+------+------+------+
  | id | body | date | type |
  +----+------+------+------+
  | 1  | foo  | 1101 | typ1 |
  | 4  | baz  | 1110 | typ1 |
  | 3  | bar  | 1115 | typ2 |
  | 3  | goo  | 1114 | typ1 |
  | 2  | fee  | 1111 | typ2 |
  +----+------+------+------+
  </code></pre>
  <p>So I need to prepare a SELECT statement that returns something like this:</p>
  <pre><code>+----+------+------+------+
  | id | body | date | type |
  +----+------+------+------+
  | 1  | foo  | 1101 | typ1 |
  | 3  | goo  | 1114 | typ1 |
  | 3  | bar  | 1115 | typ2 | &lt;- this is here because date is greater than 1114
  | 4  | baz  | 1110 | typ1 |
  | 2  | fee  | 1111 | typ2 | &lt;- this is here because date is greater than 1110
  +----+------+------+------+
  </code></pre>
  <p>What's the sorting logic and what I have to keep in mind?</p>
  <ul>
  <li><code>id</code> column is really not an ID. As you can see, <code>3</code> is repeated and the difference is the <code>type</code>.</li>
  <li><code>typ1</code> must be sorted by ID ascending (in this case <code>date</code> does not matter).</li>
  <li><code>typ2</code> must be merge into sorted <code>type1</code> by <code>date</code> (unix time).</li>
  <li>Sentence is passed to an already created method (let's suppose it's object.query(&quot;SELECT ... etc.&quot;)). So I'm not allowed to do this in multiple steps.</li>
  </ul>
  <p>Do you have any ideas? Besides basic stuff like <code>SELECT ... ORDER BY id ASC</code> I haven't been able to think of a way of working this around.</p>
  <h3>Edit</h3>
  <p>This is how the sorted table was built:</p>
  <ol>
  <li>You must order typ1 by ID:</li>
  </ol>
  <p>.</p>
  <pre><code>+----+------+------+------+
  | id | body | date | type |
  +----+------+------+------+
  | 1  | foo  | 1101 | typ1 |
  | 3  | goo  | 1114 | typ1 |
  | 4  | baz  | 1110 | typ1 |
  +----+------+------+------+
  </code></pre>
  <p>Then we have to put typ2 ordered by date, with this constraints though:</p>
  <ul>
  <li><code>typ2</code> must appear below the higher <code>typ1 date</code> which is not greater than the <code>typ2 date</code>.</li>
  </ul>