Re: Query question

If you have a row every 15 seconds, the answer is quite easy: <br /><pre>SELECTA1.date
FROMactivity A1LEFT JOIN activity A2 ON (A2.date=A1.date-'15 secs'::interval)
WHEREA1.state<>A2.state OR A2.state IS NULL
ORDER BY 1
</pre><br /><br /> Now if you don't have a row every 15 seconds, the answer is a bit more complex (at least I couldn't
thinkof an easier solution): <br /><br /><pre>SELECTmin(TMP2.new_date)
 
FROM(    SELECT        DISTINCT        TMP.new_date,        max(TMP.old_date) AS max_old_date    FROM        (
 SELECT                    A1.id AS new_id, A1.date AS new_date, A1.state AS new_state,                A2.id AS old_id,
A2.dateAS old_date, A2.state AS old_state            FROM                activity A1                LEFT JOIN activity
A2ON (A2.date<A1.date)            ORDER BY                A1.date, A2.date DESC            ) AS TMP    WHERE
TMP.old_state<>TMP.new_stateOR TMP.old_state IS NULL    GROUP BY        TMP.new_date) TMP2
 
GROUP BYTMP2.max_old_date
ORDER BY 1
</pre><br /> I've tested both queries on postgreSQL 8 with the data you provided, and they both work. Anyway try them
withlarger datasets before using them in real life <span class="moz-smiley-s3"><span> ;-) </span></span><br /><br />
Hopeit helps.<br /><br /><br /> Stéphane RIFF wrote: <blockquote cite="mid42667F3C.6080303@cerene.fr" type="cite">Hi ,
<br/><br /> I have table that represent a switch activity like this : <br /><br /> |         date                    |
state | <br /> | 2005-04-20 17:00:00 |   0     | <br /> | 2005-04-20 17:00:15 |   0     | <br /> | 2005-04-20 17:00:30
|  1     | <br /> | 2005-04-20 17:00:45 |   1     | <br /> | 2005-04-20 17:01:00 |   1     | <br /> | 2005-04-20
17:01:15|   0     | <br /> | 2005-04-20 17:01:30 |   0     | <br /> | 2005-04-20 17:01:45 |   0     | <br /><br /> I
wantto get the date of each states change but i not a sql expert. <br /> Can someone advices me <br /><br /> Thanks <br
/><br/><br /> ---------------------------(end of broadcast)--------------------------- <br /> TIP 7: don't forget to
increaseyour free space map settings <br /><br /></blockquote><br />