sql - How to group this data so I can pull out specific rows from each group -


i have dataset shown below. this, want select first row each group personids status has changed different status previous one.

for example, dataset, want rows 1, 4, 7 , 11.
on this?
if groupby, lumps new , pending in 2 groups.

personid    status  whenchanged 101         new     27/01/2017 15:27 101         new     27/01/2017 16:40 101         new     27/01/2017 16:40 101         pending 27/01/2017 16:40 101         pending 27/01/2017 16:40 101         pending 27/01/2017 16:40 101         new     31/01/2017 09:14 101         new     31/01/2017 10:02 101         new     31/01/2017 10:03 101         new     31/01/2017 10:05 101         pending 03/02/2017 14:29 101         pending 03/02/2017 14:29

here's go @ it...using cte. ordering off because date used in varchar format or displayed such. can convert same , work fine.

declare @table table (personid int, status varchar(16), whenchanged varchar(64)) insert @table values (101,'new','27/01/2017 15:27'), (101,'new','27/01/2017 16:40'), (101,'new','27/01/2017 16:40'), (101,'pending','27/01/2017 16:40'), (101,'pending','27/01/2017 16:40'), (101,'pending','27/01/2017 16:40'), (101,'new','31/01/2017 09:14'), (101,'new','31/01/2017 10:02'), (101,'new','31/01/2017 10:03'), (101,'new','31/01/2017 10:05'), (101,'pending','03/02/2017 14:29'), (101,'pending','03/02/2017 14:29')  ;with cte as(     select         personid,         status,         whenchanged,         case when lag(status) on (partition personid order convert(datetime,whenchanged,103)) <> status 1 else 0 end d             @table)    select top 1 * @table union  select  personid,         status,         whenchanged cte d=1 order whenchanged

sql server 2008 , previous editions

declare @table table (personid int, status varchar(16), whenchanged varchar(64)) insert @table values (101,'new','27/01/2017 15:27'), (101,'new','27/01/2017 16:40'), (101,'new','27/01/2017 16:40'), (101,'pending','27/01/2017 16:40'), (101,'pending','27/01/2017 16:40'), (101,'pending','27/01/2017 16:40'), (101,'new','31/01/2017 09:14'), (101,'new','31/01/2017 10:02'), (101,'new','31/01/2017 10:03'), (101,'new','31/01/2017 10:05'), (101,'pending','03/02/2017 14:29'), (101,'pending','03/02/2017 14:29')   ;with cte as(     select          personid,         status,         convert(datetime,whenchanged,103) whenchanged,         row_number() on (order personid, convert(datetime,whenchanged,103)) rn             @table),  cteresults as(     select         personid,         status,         whenchanged             cte      rn = 1     union     select         c.personid,         c.status,         c.whenchanged              cte c     inner join         cte c2 on c2.rn = (c.rn -1) , c2.status <> c.status)  select * cteresults order whenchanged

-

Comments

Post a Comment

Popular posts from this blog

python - How to insert QWidgets in the middle of a Layout? -

Image
i'm using qt framework build graphical user interface. use qgridlayout position qwidgets neatly. gui looks this: my application regularly adds new widgets gui @ runtime. these new widgets not added @ end of qlayout, somewhere in middle. the procedure bit cumbersome. applied on figure above, need take out widg_c , widg_d , ... qgridlayout. next, add widg_x and widg_y , , put other widgets again. how remove widgets qgridlayout: for in reversed(range(mygridlayout.count())): self.itemat(i).widget().setparent(none) ### as long you're dealing small amount of widgets, procedure not disaster. in application display lot of small widgets - perhaps 50 or more! application freezes second while procedure ongoing, annoying user. there way insert widgets somewhere in qlayout, without need take out other widgets? edit: apparently solution qvboxlayout simple. use function insertwidget(..) instead of addwidget(..) . docs can found link: http://doc.qt.io/qt-5/qboxlayou
Read more

python - serve multiple gunicorn django instances under nginx ubuntu -

i need serve 2 webs apps (django) on gunicorn under different domain names using nginx on ubuntu. started using tutorial: https://www.digitalocean.com/community/tutorials/how-to-set-up-django-with-postgres-nginx-and-gunicorn-on-ubuntu-16-04 it worked fine 1 using that. tried same thing second gives me 502 gateway second domain. tried following tutorial: http://michal.karzynski.pl/blog/2013/10/29/serving-multiple-django-applications-with-nginx-gunicorn-supervisor/ i'm @ part run gunicorn start script: sudo bin/gunicorn_start i response: starting webuildblack root traceback (most recent call last): file "/home/devin/webuildblack/bin/gunicorn", line 7, in <module> gunicorn.app.wsgiapp import run importerror: no module named 'gunicorn.app'; 'gunicorn' not package you have named program gunicorn.py gunicorn -package. rename file , remove gunicorn.pyc file.
Read more

module - Prestashop displayPaymentReturn hook url -

i new prestashop developer , trying create paymentmodule. have got show payment method can not proceed purchase because not know hot works. does know should redirect run hooddisplaypaymentreturn method? i happy if explain me complete navigation map make purchase. anyway, can find relation between hooks , pages? when have new module payment rely on simplest provided prestashop: bankwire. inside can find 3 hooks. hookpayment: public function hookpayment($params) { if (!$this->active) return; if (!$this->checkcurrency($params['cart'])) return; $this->smarty->assign(array( 'this_path' => $this->_path, 'this_path_bw' => $this->_path, 'this_path_ssl' => tools::getshopdomainssl(true, true).__ps_base_uri__.'modules/'.$this->name.'/' )); return $this->display(__file__, 'payment.tpl'); } hookdisplaypaymenteu: public fun
Read more