sql - numbering partitions based on changing dimensions -

-

i'm having trouble creating right partitions within data. here's example of data looks like, complete desired output:

customer   contract   type1   type2   partition 100        1                      1 100        2                      1 100        3                b       2 100        4                b       2 100        5                b       2 100        6                      3 100        7                      3 100        8          c             4 100        9          c             4

the variable i'm trying construct last one, called partition. problem have now, when using dense_rank, contracts 1 , 2 grouped contracts 6 , 7:

select   t1.* , dense_rank() on (order customer, type1, type2) partition table1 t1

what can use generate desired output (on large dataset)?

if understand correctly, want adjacent groups of rows, "adjacent" based on contract.

you can using difference of row_number() values. difference constant, when values adjacent. result provide additional grouping column provides information need:

select t1.*,        dense_rank() on (order customer, type1, type2, grp) partition (select t1.*,              (row_number() on (partition customer order contract) -               row_number() on (partition customer, type1, type2 order contract)              ) grp       table1 t1      ) t1;

Comments

Post a Comment

Popular posts from this blog

c# - Store DBContext Log in other EF table -

-
i want store logs in db table (imported ef code). my code looks this: public pmcontext() : base("myentities") { configuration.proxycreationenabled = false; configuration.lazyloadingenabled = false; this.database.log = s => logstore(s); } private void logstore(string message) { tlog log = new tlog(); log.description = message; log.insertdate = datetime.now; logs.add(log); savechanges(); } public virtual dbset<tlog> logs { get; set; } i cannot use savechanges() (get error "sqlconnection not support parallel transactions") , if remove line nothing stored in db table. how can fix this? i think happens during savechanges call anywhere in app, because sql generated call logged, triggers nested savechanges call tries start own transaction. and there thing: savechanges call in logstore method trigger logging itself, you'd end starting infin...
Read more

javascript - Karma not able to start PhantomJS on Windows - Error: spawn UNKNOWN -

-
i lost "unknown" error: karma.conf.js: frameworks: ['mocha', 'should'], plugins: ['karma-mocha', 'karma-should', 'karma-phantomjs-launcher'], ... browsers: ['phantomjs'], error: 23 07 2015 14:35:37.691:warn [karma]: no captured browser, open http://localhost:9876/ 23 07 2015 14:35:37.701:info [karma]: karma v0.13.3 server started @ http://localhost:9876/ 23 07 2015 14:35:37.707:info [launcher]: starting browser phantomjs 23 07 2015 14:35:37.738:error [karma]: { [error: spawn unknown] code: 'unknown', errno: 'unknown', syscall: 'spawn' } error: spawn unknown @ exports._errnoexception (util.js:749:11) @ childprocess.spawn (child_process.js:1093:11) @ exports.spawn (child_process.js:933:9) @ object._execcommand (c:\users\jinga4x\git\mdl-sample-project\node_modules\karma\lib\launchers\process.js:63:21) @ object._start (c:\users\jinga4x\git\md...
Read more

Nuget pack csproj using nuspec -

-
i want create nuget package contains specified in nuspec file, still version csproj. in order use token have pack like: nuget pack myproj.csproj but when adds dependencies , creates unwanted folder in nuget package. nuspec file is: <?xml version="1.0" encoding="utf-8"?> <package xmlns="http://schemas.microsoft.com/packaging/2011/08/nuspec.xsd"> <metadata> <id>test</id> <version>$version$</version> <title>test</title> <authors>test</authors> <owners>test</owners> <requirelicenseacceptance>false</requirelicenseacceptance> <description>test</description> <summary>test</summary> <releasenotes>test</releasenotes> <copyright>test</copyright> </metadata> <files> <file src="bin\debug\*.dll" target="lib\net45" /> <file s...
Read more