title: How do I build an SQL query that discards repeating data?
body: <p>I have a simple one-table SQLite3 database which stores the state of Internet reachability from my LAN's perspective.</p>
  
  <pre><code>CREATE TABLE inet_visibility (
      recid  INTEGER PRIMARY KEY,
      type   INTEGER CHECK (type &gt;= 0 AND type &lt;= 2),   -- 0 = state change; 1 = periodic; 2 = other
      ts_new TEXT UNIQUE NOT NULL,                      -- entry created; UTC ISO8601
      ts_upd TEXT UNIQUE NOT NULL,                      -- entry updated; UTC ISO8601
      updcnt INTEGER NOT NULL,                          -- update tally
      state  INTEGER CHECK (state &gt;= 0 AND state &lt;= 2), -- 0 == unreachable; 1 = reachable; 2 = unknown
      byhost TEXT NOT NULL,                             -- IP of tester
      method TEXT NOT NULL,                             -- tool/process used to determine state
      note   TEXT DEFAULT ''                            -- optional note/comment
  );
  </code></pre>
  
  <p>After several days of running, I've accumulated the following data:</p>
  
  <pre><code>recid       type        ts_new               ts_upd               updcnt      state       byhost        method      note
  ----------  ----------  -------------------  -------------------  ----------  ----------  ------------  ----------  ----------
  1           0           2019-05-20 10:31:48  2019-05-22 14:45:10  3106        1           192.168.1.47  heartbeat
  2           0           2019-05-22 14:46:11  2019-05-23 07:41:52  981         0           192.168.1.47  ping
  3           0           2019-05-23 07:42:52  2019-05-26 10:11:14  4438        1           192.168.1.47  heartbeat
  4           0           2019-05-26 10:12:15  2019-05-26 10:12:15  0           1           192.168.1.47  ping
  5           0           2019-05-26 10:13:24  2019-05-27 09:58:36  1415        1           192.168.1.47  heartbeat
  6           0           2019-05-27 09:59:37  2019-05-27 09:59:37  0           1           192.168.1.47  ping
  7           0           2019-05-27 10:00:49  2019-05-30 09:48:29  4277        1           192.168.1.47  heartbeat
  8           0           2019-05-30 09:49:29  2019-05-30 09:49:29  0           1           192.168.1.47  ping
  9           0           2019-05-30 09:50:34  2019-06-02 19:42:50  4877        1           192.168.1.47  heartbeat
  10          0           2019-06-02 19:44:33  2019-06-02 19:44:33  0           0                         ping
  11          0           2019-06-02 19:46:00  2019-06-03 10:32:44  881         1           192.168.1.47  heartbeat
  </code></pre>
  
  <p>For the primary report, I'm only interested in the change of <code>state</code> at the earliest time. So for this report, I want the following data:</p>
  
  <pre><code>recid       type        ts_new               ts_upd               updcnt      state       byhost        method      note
  ----------  ----------  -------------------  -------------------  ----------  ----------  ------------  ----------  ----------
  1           0           2019-05-20 10:31:48  2019-05-22 14:45:10  3106        1           192.168.1.47  heartbeat
  2           0           2019-05-22 14:46:11  2019-05-23 07:41:52  981         0           192.168.1.47  ping
  3           0           2019-05-23 07:42:52  2019-05-26 10:11:14  4438        1           192.168.1.47  heartbeat
  10          0           2019-06-02 19:44:33  2019-06-02 19:44:33  0           0                         ping
  11          0           2019-06-02 19:46:00  2019-06-03 10:32:44  881         1           192.168.1.47  heartbeat
  </code></pre>
  
  <p>The two key requirements to note from the above four-line extract are these:
      a) each <code>state</code> value alternates; and b) rec 3 (the earliest) is returned, not recs 4 through 9</p>
  
  <p>I have no idea how to write a query to get these results.
  <code>sqlite3 history.db 'select * from inet_visibility group by state order by ts_upd asc'</code>
  ... gives me only records 1 and 2.
  <code>sqlite3 history.db 'select * from inet_visibility group by date(ts_upd),state order by ts_upd asc'</code>
  ... is a bit closer but too much data: I see records 5, 7, and 9.
  I know both these queries are wrong; I was trying to home in on a solution and hit a wall (in my SQL skills).</p>
  
  <p>In essence ... If you consider the data as an order list by ascending <code>ts_upd</code>, I only want the records where <code>state</code> changes relative to the previous record.</p>
  
  <p>An aside: for this primary report, I only care about <code>recid</code>, <code>ts_upd</code>, and <code>state</code> - the rest of the columns can be ignored, if that matters.</p>
  
  <p>Can someone offer a suggestion or a solution?</p>