Nikolas Everett wrote:
> Sorry. Here is the setup:
> CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2 INT
> NOT NULL, timestamp TIMESTAMP);
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
> '12 hours');
> INSERT INTO test (state1, state2, tim
Got it:
SELECT state1, timestamp
FROM (SELECT state1, timestamp, lag(state1) OVER (ORDER BY timestamp)
FROM test) as foo
WHERE state1 != lag OR lag IS NULL
ORDER BY timestamp;
state1 | timestamp
+
1 | now() - interval '12 hours'
The 10 and 11 hour interval are being skipped because I'm only interested in
the transitions of state 1. State 1 only transitioned three times at now -
12, now - 9 and now - 8.
The table has both transitions in it because I frequently care about them
both together. I just don't in this case.
On
On 5/27/2010 9:45 AM, Nikolas Everett wrote:
> Sorry. Here is the setup:
> CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL,
> state2 INT NOT NULL, timestamp TIMESTAMP);
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
> interval '12 hours');
> INSERT INTO tes
On 5/27/2010 9:04 AM, Nikolas Everett wrote:
> Say I have a table that stores state transitions over time like so:
> id, transitionable_id, state1, state2, timestamp
>
> I'm trying to write a query that coalesces changes in state2 away to
> produce just a list of transitions of state1. I guess it
Sorry. Here is the setup:
CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2 INT
NOT NULL, timestamp TIMESTAMP);
INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
'12 hours');
INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() - interva
Lost me a bit, do you mean DISTINCT?
select distinct state1, first(timestamp) from table
On May 27, 2010, at 10:04 AM, Nikolas Everett wrote:
> Say I have a table that stores state transitions over time like so:
> id, transitionable_id, state1, state2, timestamp
>
> I'm trying to write a qu
