StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Just so that I understand the problem.</p> <p>You have, if you order the row set based on the timestamp, sometimes <em>duplicate</em> values occuring next to each other, like the above pair of 1's in the 2nd and 3rt item? and then you have double 0's in the 4th and 5th, is that it?</p> <p>And you want the last of the corresponding pair (or sequence if there are more than 2)?</p> <p>Why do you need to remove them? I'm asking because unless they occupy a significant share of the size of this table, it might be easier to filter them out like you do sequentially when you need to process or display them.</p> <p>A solution, though not a very good one, would be to retrieve the minimum timestamp <em>above</em> the timestamp of the current row you're examining, and then retrieve the value from that, and if it's the same, don't return the current row.</p> <p>Here's the SQL to get everything:</p> <pre><code>SELECT timestamp, value FROM yourtable </code></pre> <p>And here's how to join in to get the minimum timestamp above the current one:</p> <pre><code>SELECT T1.timestamp, MIN(T2.timestamp) AS next_timestamp, T1.value FROM yourtable T1, yourtable T2 WHERE T2.timestamp > T1.timestamp GROUP BY T1.timestamp, T1.value </code></pre> <p>(I fear the above query will be horribly slow)</p> <p>And then to retrieve the value corresponding to that minimum timestamp</p> <pre><code>SELECT T3.timestamp, T3.value FROM ( SELECT T1.timestamp, MIN(T2.timestamp) AS next_timestamp, T1.value FROM yourtable T1, yourtable T2 WHERE T2.timestamp > T1.timestamp GROUP BY T1.timestamp, T1.value ) T3, yourtable AS T4 WHERE T3.next_timestamp = T4.timestamp AND T3.value <> T4.value </code></pre> <p>Unfortunately this doesn't produce the last value, as it needs a following value to compare against. A simple dummy sentinel-value (you can union that in if you need to) will handle that.</p> <p>Here's the sqlite database dump I tested the above query against:</p> <pre><code>BEGIN TRANSACTION; CREATE TABLE yourtable (timestamp datetime, value int); INSERT INTO "yourtable" VALUES('2008-09-22 16:28:14.133',0); INSERT INTO "yourtable" VALUES('2008-09-22 16:28:35.233',1); INSERT INTO "yourtable" VALUES('2008-09-22 16:29:16.353',1); INSERT INTO "yourtable" VALUES('2008-09-22 16:31:37.273',0); INSERT INTO "yourtable" VALUES('2008-09-22 16:35:43.134',0); INSERT INTO "yourtable" VALUES('2008-09-22 16:36:39.633',1); INSERT INTO "yourtable" VALUES('2008-09-22 16:41:40.733',0); INSERT INTO "yourtable" VALUES('2099-12-31 23:59:59.999',2); COMMIT; </code></pre> <p>And here is the (formatted) output:</p> <pre><code>timestamp value 2008-09-22 16:28:14.133 0 2008-09-22 16:29:16.353 1 2008-09-22 16:35:43.134 0 2008-09-22 16:36:39.633 1 2008-09-22 16:41:40.733 0 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload