sql - how does first_value and last_value works with range unbounded preceding and current row? - 


select      empno,deptno,sal,hiredate,extract(year hiredate),     last_value(sal) over(partition deptno order extract(year hiredate) ) emp;  7782    10  2450    09-jun-81   1981    5000 7839    10  5000    17-nov-81   1981    5000 7934    10  1300    23-jan-82   1982    1300 7369    20  800     17-dec-80   1980    800 7566    20  2975    02-apr-81   1981    3000 7902    20  3000    03-dec-81   1981    3000 7876    20  1100    23-may-87   1987    3000 7788    20  3000    19-apr-87   1987    3000 7900    30  950     03-dec-81   1981    2850 7654    30  1250    28-sep-81   1981    2850 7521    30  1250    22-feb-81   1981    2850 7844    30  1500    08-sep-81   1981    2850 7499    30  1600    20-feb-81   1981    2850 7698    30  2850    01-may-81   1981    2850  select      empno,deptno,sal,hiredate,extract(year hiredate),     first_value(sal) over(partition deptno order extract(year hiredate)) emp;  7782    10  2450    09-jun-81   1981    2450 7839    10  5000    17-nov-81   1981    2450 7934    10  1300    23-jan-82   1982    2450 7369    20  800     17-dec-80   1980    800 7566    20  2975    02-apr-81   1981    800 7902    20  3000    03-dec-81   1981    800 7876    20  1100    23-may-87   1987    800 7788    20  3000    19-apr-87   1987    800 7900    30  950     03-dec-81   1981    950 7654    30  1250    28-sep-81   1981    950 7521    30  1250    22-feb-81   1981    950 7844    30  1500    08-sep-81   1981    950 7499    30  1600    20-feb-81   1981    950 7698    30  2850    01-may-81   1981    950

the first_value function returns first value in ordered set. in above example returns first salary value of each department.

but last_value not return expected output should return last_value in ordered set. here last_value() works differently considering last value respect year(which mentioned in order clause)

why work differently?

if omit windowing clause, defaults range between unbounded preceding , current row in case of first_value , last_value functions.

that means, each record within partition, starting point of window first record. so, same value rows in first_value functions.

however, end point current row itself, keeps on changing each row. so, value of current row value last_value function. but, if order clause not enough reliably identify order, there tie , rows arbitrarily ordered, in example, department 30.


Comments

Post a Comment

Popular posts from this blog

javascript - Using jquery append to add option values into a select element not working -

i'm looking dynamically populate select element names of items stored array. for reason it's not working , i'm not sure why. html: <div class="col-md-4 col-md-offset-4"> <select class="select1"> </select> , <select class="select2"> </select> </div> note i'm using bootstrap layout. here's jquery i'm using try , populate ".select1" jquery: select: function() { $.each(state.greens, function(index, value) { $(".select1").append("<option value =' " + index + " '>" + state.greens[index].name + "</option>"); }); } note function 'select' stored within object called 'display'. state.greens name of array i'm trying access. so @ end of html page call display.select(); call function. no error messages showing in console. also, saw question: appending ...
Read more

Android soft keyboard reverts to default keyboard on orientation change -

i'm building android soft keyboard , can't seem fix bug - have arabic , qwerty keyboard , when rotate device on qwerty keyboard (or arabic shift), it's if program has "restarted" , becomes arabic keyboard without shift. the onsaveinstancestate(bundle savedinstancestate) not work because application not extend activity inputmethodservice . i put following in android manifest android:configchanges="keyboard|keyboardhidden|orientation" android:windowsoftinputmode="stateunchanged|adjustresize"> i tried using @override public void onconfigurationchanged(configuration newconfig) { super.onconfigurationchanged(newconfig); log.i(mydebug, "config changed " + currentkeyboard.equals(qwerty)); } however, currentkeyboard.equals(qwerty)) results false , made sure true before orientation change. any appreciated. i think applications fault. if application restarting on orientation change...
Read more

Rendering JButton to get the JCheckBox behavior in a JTable by using images does not update my table -

i rendering jbutton image on simulate jcheckbox behavior. why don't let jtable render jcheckbox? because need bigger checkboxes , there's no way can resize them (as far know). so, have own table cell renderer , editor. both renders jbutton , checkbox images on them. trying change image checked image unchecked image (or vice versa) , update cell value (boolean true or false). however, reason, getcelleditorvalue() not update table. so, release mouse, value goes original one. any appreciated! lot! public class tableexample extends jframe { jscrollpane pane; jpanel panel; jtable table; object[][] data; mytablemodel tm; renderer buttoncolumn; public tableexample(object[][] data) throws ioexception { string[] columns = {"column#1", "column#2", "column#3", "column#4", "column#5", "column#6"}; this.data = data; this.setpreferredsize(new dimension(700, 500)); ...
Read more