Re: Please help me write a query

Nikolas Everett <nik9000(at)gmail(dot)com> 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