rank - SQL: Ranking Sections separately of a Rollup over multiple columns -

-

i try rollup on multiple columns , apply ranking on each stage/section of rollup process. result should following:

| cola | colb | colc | rankingcriteria | ranking | |------|------|------|-----------------|---------| | -    | -    | -    | 10              | 1       | |------|------|------|-----------------|---------| |    | -    | -    | 10              | 1       | | b    | -    | -    | 8               | 2       | |------|------|------|-----------------|---------| |    |    | -    | 9               | 1       | |    | b    | -    | 7               | 2       | |    | c    | -    | 5               | 3       | |    | d    | -    | 2               | 4       | |------|------|------|-----------------|---------| | b    |    | -    | 8               | 1       | | b    | c    | -    | 7               | 2       | | b    | b    | -    | 2               | 3       | |------|------|------|-----------------|---------| |    |    | x    | 7               | 1       | |    |    | y    | 5               | 2       | |    |    | z    | 4               | 3       | |------|------|------|-----------------|---------| |    | b    | y    | 6               | 1       | |------|------|------|-----------------|---------| |    | c    | w    | 10              | 1       | |    | c    | y    | 10              | 1       | |    | c    | z    | 8               | 2       | |    | c    | x    | 6               | 3       | |------|------|------|-----------------|---------| |    | d    | y    | 4               | 1       | |------|------|------|-----------------|---------| | b    |    | w    | 10              | 1       | | b    |    | x    | 8               | 2       | |------|------|------|-----------------|---------| | b    | b    | y    | 6               | 1       | | b    | b    | z    | 5               | 2       | | b    | b    | w    | 4               | 3       | |------|------|------|-----------------|---------| | b    | c    | x    | 6               | 1       | |------|------|------|-----------------|---------|

so can see each grouping set has it's own ranking.

the basic rollup-query simple ranking giving me headaches , running out of ideas on how achieve this.

select cola, colb, colc, rankingcriteria table group rollup(cola, colb, colc)

the problem cannot use normal rank() on (partition ...) because there no partition use that'd work on whole thing.

i think produce want:

select r.*,        row_number() on (partition (case when colb null , colc null , cola not null                                              1 else 0 end),                                        (case when colb null , colc null , cola not null                                              null else end),                                        (case when colb null , colc null , cola not null                                              null else b end)                          order rankingcriteria desc) seqnum                            (select cola, colb, colc, rankingcriteria       table       group rollup(cola, colb, colc)      ) r;

the way read logic partitioning , b works second group. why uses 3 case statements.


Comments

Post a Comment

Popular posts from this blog

c - Bitwise operation with (signed) enum value -

-
i using enumerator values flags: typedef enum { = 0x00, b = 0x01u, // u has no influence, expected c = 0x02u, // u has no influence, expected ... } enum_name; volatile unsigned char* reg = someaddress; *reg |= b; according misra-c:2004 bitwise operations shall not done signed type. unfortunately, compiler iar use signed int (or short or char) underlying type of enums, , option can find relates size, not signedness ("--enum-is-int"). according iar c/c++ development guide arm , pages 169 , 211, can define type of enum s if enable iar language extensions ( -e command-line option, or project > options > c/c++ compiler > language > allow iar extensions in ide). in particular, should define "sentinel" value, make sure compiler chooses correct type. prefers signed types, , uses smallest possible integer type, sentinel should largest positive integer corresponding unsigned integer type can describe. example, typedef enum
Read more

xslt - Unnest parent nodes by child node -

-
in xml there items 0-n attributes , item should copied each attribute new item 1 attribute. given xml this: <?xml version="1.0" encoding="utf-8" ?> <items> <item> <name>a</name> <attributes> <attribute> <key>attribute1</key> <value>1</value> </attribute> </attributes> </item> <item> <name>b</name> </item> <item> <name>c</name> <attributes> <attribute> <key>attribute1</key> <value>5</value> </attribute> <attribute> <key>attribute2</key> <value>2</value> </attribute> <attribute> <key>attri
Read more

YouTubePlayerFragment cannot be cast to android.support.v4.app.Fragment -

-
im stuck on 3 days , have tried have come across after lots of searching ,this post last resort, im trying open youtubeplayerfragment using navigation drawer youtubeplayerfragment cannot cast android.support.v4.app.fragment problem edit question : have updated code have used youtubeplayersupportfragment instead of youtubeplayerfragment ,i error in displayview method (case 4: fragment = new youtube(); break;) in main activity saying cannot convert youtube fragment , cannot logcat since cant run it edit question 2: code updated again have runtime problem ,heres logcat 07-22 22:15:09.782: e/androidruntime(20603): fatal exception: main 07-22 22:15:09.782: e/androidruntime(20603): process: net.frankandwalters, pid: 20603 07-22 22:15:09.782: e/androidruntime(20603): java.lang.nullpointerexception: attempt invoke virtual method 'void com.google.android.youtube.player.youtubeplayerview.a()' on null object reference 07-22 22:15:09.782: e/androidruntime(20603): @ com.goog
Read more