Nikolas Everett <nik9000@xxxxxxxxx> 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, 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? Partitioning usually means splitting data across several tables for faster access which is probably not what you want here. A simple solution would be to use LAG() and discard rows where the current value is equal to the preceding value: | SELECT state1, timestamp | FROM | (SELECT id, | state1, | state2, | LAG(state1) OVER (ORDER BY timestamp) AS prevstate1, | timestamp FROM test) AS SubQuery | WHERE state1 IS DISTINCT FROM prevstate1 | ORDER BY timestamp; Tim -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
