I need to find highest voters in each year Oracle SQL query -


i have 1 table lets ranking info has 

username   mvid  votedate john        1    23-sep-90 john        2    23-sep-90 smith       1    23-sep-90 john        3    24-oct-91 smith       3    24-oct-91 smith       4    25-dec-91 smith       5    25-dec-91

i need write sql query in sqldeveloper(oracle) give me member has given largest number of votes in each year. output should username,year, total number of votes in each year. lets consider above example: need output this.

username  year  number_of_votes john       1990    2 smith      1991    3

because in 1990 john beat smith 1 vote while in 1991 smith beat john 2 votes.

i point counted votes not maximum number of votes in year.

this have done:

select r1.username,      extract(year r1.votedate)"year",     count(username) rankinginfo r1 extract(year r1.votedate)  not null group extract(year r1.votedate),        r1.username; order  extract(year r1.votedate),       username; 

select  *    (         select  votesperuserperyear.*         ,       dense_rank() on (                     partition voteyear                     order votecount desc) rn            (                 select  username                 ,       extract(year votedate) voteyear                 ,       count(*) votecount                    yourtable                 group                         username                 ,       extract(year votedate)                 ) votesperuserperyear         ) subquerywithrank   rn = 1 -- top voter per year

example @ sql fiddle.


Comments

Post a Comment

Popular posts from this blog

linux - xterm copying to CLIPBOARD using copy-selection causes automatic updating of CLIPBOARD upon mouse selection -

i've tried various ways coerce xterm (versions 285 , 292) copy selection clipboard clipboard whenever press ctrl-shift-c. promising way far has been, in ~/.xresources, put this: xterm*vt100.translations: #override \ ctrl shift <keypress> c: copy-selection(clipboard) \n\ ctrl shift <keypress> v: insert-selection(clipboard) ctrl-shift-v works perfectly, copying has nuance... if restart xterm, highlighting text puts things in primary clipboard; expected, proper, default behavior. if hit ctrl-shift-c, copies current selection clipboard clipboard. the bug, however, if highlight text after first time pressed ctrl-shift-c, you'll see highlighting copies both primary and clipboard. cannot figure out how stop xterm updating clipboard upon selection... , doesn't make sense me in first place. i said, @ specific point in time, copy selection clipboard... yet starts updating automatically upon selection after doing once... anyone have workaround...
Read more

c++ - qgraphicsview horizontal scrolling always has a vertical delta -

Image
i've subclassed qgraphicsview custom canvas used in cad application. i've reimplemented qgraphicsview::wheelevent check keyboard modifiers control key and, if control key pressed, zoom. i'm trying implement horizontal scroll when user holds shift , uses wheel. the problem i'm having horizontal scrolling always scrolls 0.279. not huge problem, hugely annoying , points else being wrong. so, here questions: is right way implement horizontal scrolling? if not, is? how eliminate delta of 0.279? thanks in advance. code , sample output below void myview::zoom(int delta) { double factor = pow(1.2, delta/abs(delta)); this->scale(factor, factor); } void myview::scrollhorizontal(int level) { qpointf center = maptoscene(viewport()->rect().center()); qdebug() << "center: " << center.x() << ", " << center.y(); centeron(qpointf(center.x() - level, center.y())); } void myview::wheelevent(qwheeleve...
Read more