mysql - SQL: selecting rows where column value changed from previous row -


let's have (mysql) database, sorted increasing timestamp:

timestamp   system statusa statusb  2011-01-01          ok     ok       2011-01-02     b      ok     ok      2011-01-03         fail   fail      2011-01-04     b      ok    fail      2011-01-05         fail    ok       2011-01-06          ok     ok       2011-01-07     b     fail   fail

how select rows statusa changed previous row system? statusb doesn't matter (i show in question illustrate there may many consecutive rows each system statusa doesn't change). in example above, query should return rows 2011-01-03 (statusa changed between 2011-01-01 , 2011-01-03 systema), 2011-01-06, 2011-01-07.

the query should execute table having tens of thousands of records.

thanks

select a.* tablex a.statusa <>       ( select b.statusa         tablex b         a.system = b.system           , a.timestamp > b.timestamp         order b.timestamp desc         limit 1       )

but can try (with index on (system,timestamp):

select system, timestamp, statusa, statusb   ( select (@statuspre <> statusa , @systempre=system) statuschanged          , system, timestamp, statusa, statusb          , @statuspre := statusa          , @systempre := system     tablex        , (select @statuspre:=null, @systempre:=null) d     order system            , timestamp   ) statuschanged ;

Comments

Post a Comment

Popular posts from this blog

sql - text truncated using perl DBI insert -

the problem dbi 's insert leaves long string truncated when inserting ms sql server. here codes: my $insert = $dbh->prepare("insert my_table (field_1, field_2) values (?, ?)"); $insert->execute($value_1, $value_2); where field_2 has data type of varchar(100) , $value_2 text string of 90 characters spaces no other special characters. after statement executed, no error raised, checked database , apparently actual inserted $value_2 truncated @ 80th character, in middle of regular english word (i.e. not special character). i've tried alter data type of field_2 varchar(150) , text . i've used $dbh->quote($value_2) in place of $value_2 . didn't help. why happening? should do? thx!! if using freetds bug identified in freetds mailing list. see freetds silently truncating text/varchar/etc fields 80 characters , http://lists.ibiblio.org/pipermail/freetds/2011q2/026943.html , http://lists.ibiblio.org/pipermail/freetds/2011q2/026...
Read more

c++ - Is it possible to compile a VST on linux? -

for class project i'm attempting write vst plugin backed cuda. current cuda workflow on linux box, i'd prefer compile , link there. according wikipedia , should possible (i couldn't find steinberg documentation relevant linux) can't find makefile or instructions on how build if aren't using xcode or visual studio. i'm vst 3 sdk doesn't support linux. when try compile plugin under linux, error: ./base/source/fatomic.cpp:39:30: fatal error: libkern/osatomic.h: no such file or directory this issue caused following code in "vst3 sdk/base/source/fatomic.cpp" #if mac #include <libkern/osatomic.h> #if mac_os_x_version_min_required > mac_os_x_version_10_4 #define native_atomic_type (volatile int32_t*) #else #define native_atomic_type (int32_t*) #endif #elif windows #include <windows.h> #endif but hope compiling under linux work vst sdk 2.4. reading. jvstwrapper seems run on lin...
Read more

php - Pass object by reference or value -

i'm sure question has been asked thousand times, had trouble finding answer understand or use anywhere. in project, need use sql class , other misc classes, in alot of classes. i'm qurious know what's best way performance wise pass objects. should pass objects classes construct reference? class myclass { private $_link; function __construct(&$db) { $this->_link =& $db; } } or value.. class myclass { private $_link; function __construct($db) { $this->_link = $db; } } or create new object? class myclass { private $_link; function __construct() { $this->_link = new db(); } } if using php5+, in cases, objects passed reference default.
Read more