increment row number when value of field changes in Oracle -


i need in writing query in oracle following data. data sorted person , day fields.

    person     day   flag     ------     ---   ----      person1    day1   y     person1    day2   y     person1    day3   y     person1    day4   n     person1    day5   n     person1    day6   y     person1    day7   y     person1    day8   y

i need have group_number column gets incremented whenever flag value changes. result should below

    person     day   flag  group_number     ------     ---   ----  ------------     person1    day1   y    1     person1    day2   y    1     person1    day3   y    1     person1    day4   n    2     person1    day5   n    2     person1    day6   y    3     person1    day7   y    3     person1    day8   y    3

i think there way above result using analytic functions such row_number, lead etc.

you can combine analytic functions sum (used running total) , lag:

sql> data (   2            select 'person1' person, 'day1' day, 'y' flag dual   3  union select 'person1' person, 'day2' day, 'y' flag dual   4  union select 'person1' person, 'day3' day, 'y' flag dual   5  union select 'person1' person, 'day4' day, 'n' flag dual   6  union select 'person1' person, 'day5' day, 'n' flag dual   7  union select 'person1' person, 'day6' day, 'y' flag dual   8  union select 'person1' person, 'day7' day, 'y' flag dual   9  union select 'person1' person, 'day8' day, 'y' flag dual  10  )  11  select person, day, flag, sum(gap) on (partition person  12                                           order day) grp  13    (select person, day, flag,  14                 case when flag = lag(flag) on (partition person  15                                                  order day)  16                      0  17                      else 1  18                 end gap  19            data);  person  day  flag        grp ------- ---- ---- ---------- person1 day1 y             1 person1 day2 y             1 person1 day3 y             1 person1 day4 n             2 person1 day5 n             2 person1 day6 y             3 person1 day7 y             3 person1 day8 y             3

Comments

Post a Comment

Popular posts from this blog

Change php variable from jquery value using ajax (same page) -

what i'm trying this: i've php global variable $currentid , simple jquery script check if there "id" (page) in hash of url , if yes wanted give value $currentid . i've been reading lot , notice shall use ajax i'm not managing it. my code is: <script type="text/javascript"> $(document).ready(function(){ if (parent.location.hash != '' ) { var thecurrentid = parent.location.hash.split('#')[1]; $.ajax({ url: 'my.php', //current page type: 'post', data: {theid: thecurrentid }, datatype: 'json', success: function () { } }); } // code here }); </script> could me please? thank in advance. php server side language, meaning once has sent data browser, php variables , data cannot changed... however, using ajax, can send request php page , change data in dom based on result. consider th...
Read more

Pull out data related to my apps from Android Play Store and iOS App Store -

i want pull out data related apps (ratings, reviews, etc) google play store , apple app store ? how can accomplish ? i have found open source apis google mentioned not reliable. also there paid apis (e.g. appfigures) available. has idea how able pull data these play stores ?? i looking direction on how can accomplish this. can suggest ? we (appfigures) have been working on developing infrastructure give ability reviews , bunch of other data points major app stores several years :] we make available via our api though may want check out.
Read more

How can I fetch data from a web server in an android application? -

i want retrieve data web server in android application, , don't know begin. should use web services? i recommend these tutorials: connect android php , mysql , json in android , php , mysqli i used these tutorials , managed trying working without difficulty. between them describe each step in how attempting @ each stage, android application, database , web server side , has information included can process , use received information the thing add connect android php , mysql tutorial makes use of mysql_ in php deprecated. better use mysqli why included third link. the basic outline of want this: 1) in android app make request server php script using class this: import java.io.bufferedreader; import java.io.ioexception; import java.io.inputstream; import java.io.inputstreamreader; import java.io.unsupportedencodingexception; import java.util.list; import org.apache.http.httpentity; import org.apache.http.httpresponse; import org.apache.http.namevaluepair...
Read more