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'
2 | now() - interval '9 hours'
1 | now() - interval '8 hours'
Without lag IS NULL I miss the first row.
On Thu, May 27, 2010 at 11:44 AM, Nikolas Everett <[email protected]> wrote:
> 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 Thu, May 27, 2010 at 12:36 PM, Justin Graf <[email protected]> wrote:
>
>> 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 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'
>>
>>
>> Have a question what makes these values different other than the
>> timestamp???
>>
>>
>> 1, 1, now() - interval '12 hours'
>> *1, 1, now() - interval '10 hours'*
>>
>> The reason i ask, is because you show *1, 1, now() - interval '8 hours'*
>> in the desired output. What logic keeps the 8 hour and 12 hour but not the
>> 10hour interval???
>>
>> Its kinda hard to understand why the 10hour interval is being skipped???
>>
>>
>> All legitimate Magwerks Corporation quotations are sent in a .PDF file
>> attachment with a unique ID number generated by our proprietary quotation
>> system. Quotations received via any other form of communication will not be
>> honored.
>>
>> CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain
>> legally privileged, confidential or other information proprietary to
>> Magwerks Corporation and is intended solely for the use of the individual to
>> whom it addresses. If the reader of this e-mail is not the intended
>> recipient or authorized agent, the reader is hereby notified that any
>> unauthorized viewing, dissemination, distribution or copying of this e-mail
>> is strictly prohibited. If you have received this e-mail in error, please
>> notify the sender by replying to this message and destroy all occurrences of
>> this e-mail immediately.
>> Thank you.
>>
>
>
