Re: Please help me write a query

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 <justin(at)magwerks(dot)com> 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.
>