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() - interval '11 hours');
INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval '10 hours');
INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() - interval '9 hours');
INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval '8 hours');
I want to write a query that spits out:
state1 | timestamp
--------+----------------------------
1 | now() - interval '12 hours'
2 | now() - interval '9 hours'
1 | now() - interval '8 hours'
Standard grouping destroys the third row so that's out. No grouping at all gives repeats of state1. Is this what partitioning is for?
Nik
On Thu, May 27, 2010 at 10:20 AM, Ozz Nixon <ozznixon@xxxxxxxxx> wrote:
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 query that coalesces changes in state2 away to produce just a list of transitions of state1. I guess it would look something like
>
> SELECT state1, FIRST(timestamp)
> FROM table
>
> but I have no idea how to aggregate just the repeated state1 rows.
