sql server - Row_Number() partitioning according to consecutive rows -

-

i working on query sql server 2008 needs partition in way considers consecutive nature of rows in table, meaning has no "memory" , restart row numbering when consecutiveness breaks down partition.

to illustrate:

declare @test table  ( customerid  varchar(10), itemid  varchar(10), platformname varchar(10), date    datetime )  insert @test values ('aaaa', 'x', 'mobile','2015-10-24 22:52:47') insert @test values ('aaaa', 'x', 'mobile','2015-10-23 22:56:47') insert @test values ('aaaa', 'k', 'mobile','2015-10-22 21:52:47') insert @test values ('aaaa', 'k', 'tablet','2015-10-20 22:12:47') insert @test values ('aaaa', 'x', 'mobile','2015-10-19 20:52:47') insert @test values ('aaaa', 'k', 'tablet','2015-10-18 12:52:47') insert @test values ('aaaa', 'k', 'tablet','2015-10-16 12:52:47')  select t.*, row_number() on (partition t.customerid,t.itemid,t.platformname order        t.date desc) rowno @test t order t.date desc

the following query returns:rowno

1 2 1 1 3 2 3

instead of desired: 

1  2  1  1  1  1  2

in case of row 5 , 6 should restart counting because new partition when consider consecutiveness breaks apart initial partition.

i need rank rows in accordance row numbering, follows:

1  1  2  3  4  5  6  7  7

what want create indicator changes when partition changes. can following trick. since row number increments within given partition, if subtract incrementing number within every row same number whole partition sequence.

here chart @ start of partition.

 row number     partition row number     row number-partition number          x                  1                     x-1      x+1                2                     x-1      ...      x+n                n+1                   x-1

x change @ next partition partition number start @ 1 , same number every row in partition until next sequential partition.

you use result part of partition , problem solved.

here how code in sql:

with cte as(select *, row_number() over(order date desc)               - row_number() over(partition customerid, itemid, platformname                                             order date desc) rn @test) select *, row_number() over(partition customerid, itemid, platformname, rn                                              order date desc) rn2  cte order date desc

Comments

Post a Comment

Popular posts from this blog

javascript - Chart.js (Radar Chart) different scaleLineColor for each scaleLine -

-
Image
i try , create radar chart using chart.js has various colours each scaleline, or coloured between scalelines. wondering if possible? from: to: i have working graph, though there doesn't seem method change individual scale lines. kind regards leigh you can extend radar chart type this, so chart.types.radar.extend({ name: "radaralt", initialize: function (data) { chart.types.radar.prototype.initialize.apply(this, arguments); var originalscaledraw = this.scale.draw; var ctx = this.chart.ctx; this.scale.draw = function () { var linewidth = this.linewidth; // bypasses line drawing in originalscaledraw this.linewidth = linewidth; originalscaledraw.apply(this, arguments); ctx.linewidth = this.linewidth; var scale = this; // draw chart.helpers.each(scale.ylabels, function (label, index) { // color of ...
Read more

apache - Error with PHP mail(): Multiple or malformed newlines found in additional_header -

-
suddenly have started receiving above error without changes having been made script. host 1and1 (i know...) the script still works fine on different server, , suspicion there must have been server config change has lead this, although hosts plead ignorance. there's no information on above error @ in google can find - have ideas? server running apache if helps. had similar problem. came out of blue. no php code changed. what changed: php upgraded 5.5.25-1 5.5.26. a security risk in php mail() function has been fixed , newlines in additional_headers allowed no more. because newlines mean: starts email message (and surely don't want inject newlines through headers followed evil message). what have worked fine, e.g. having newlines after headers or passing whole message additional_headers , function no more. solution : sanitize headers. no multiple newlines in additional_headers argument. these count "multiple or malformed newlines":...
Read more

android - Go back to previous fragment -

-
btw second fragment. appear once press button in first fragment. in fragment, if press button on phone, go previous fragment. need guys. in advance. public class listalldoctorfragment extends fragment { button close; @nullable @override public view oncreateview(layoutinflater inflater, viewgroup container, bundle savedinstancestate) { view view = inflater.inflate(r.layout.listalldoctor, container,false); close = (button) view.findviewbyid(r.id.button); close.setonclicklistener(new view.onclicklistener() { @override public void onclick(view view) { android.app.fragmentmanager fm = getfragmentmanager(); fragmenttransaction ft = fm.begintransaction(); shit f2 = new shit(); ft.add(r.id.content_frame, f2); ft.addtobackstack(null); ft.commit(); } }); return view; } } assuming want g...
Read more