MySQL If Statement and Increment -


i having issues mysql if statement creates group rank. here mysql statement:

select encode, enname, quscore,  @scorerank := if(@currathlete = encode, @scorerank + 1, 1), @currathlete := encode ranking order encode, quscore desc

it gives following output

'1004277','ashe','1628','1','1004277' '1004277','ashe','1309','1','1004277' '1004277','ashe','1263','1','1004277' '1004277','ashe','648','1','1004277' '1004277','ashe','645','1','1004277' '1004277','ashe','1628','1','1004277' '1015934', 'sabina', '544', '1', '1015934' '1015934', 'sabina', '455', '1', '1015934' '1015934', 'sabina', '276', '1', '1015934' '1015934', 'sabina', '216', '1', '1015934'

what should doing incrementing each of '1' numbers 1 each row has same code, , starting 1 again when sees different code number (1004277, 1015934 in case)

any appreciated have followed number of examples online using above method seem hit same issue point.

try way in stored procedure:

drop procedure if exists incrementme; create procedure incrementme()  begin  declare oldennamevar varchar(10) default null; declare done int default false; declare encodevar varchar(10); declare ennamevar varchar(10); declare quscorevar varchar(10);  declare scorerankvar varchar(10); declare currathalthletevar varchar(10); declare countcode int(29) default(1); declare counter int(20) default 0;  declare  get_cur  cursor select `encode`,`enname`,`quscore`,`scorerank`,`currathalthlete` tbl_ranking; declare continue handler sqlstate '02000' set done=1; drop table if exists temp_temptable; create temporary table temp_temptable(encodevar varchar(50) null,ennamevar varchar(50)  null,quscorevar varchar(50)  null,scorerankvar varchar(50)  null,currathalthletevar varchar(50)  null,recordcount int(10)  null);  open get_cur;  repeat  set counter = counter + 1;         fetch get_cur encodevar,ennamevar,quscorevar,scorerankvar,currathalthletevar;           if (oldennamevar = ennamevar)              set countcode = countcode +1;          else             if(counter=1)                                 set countcode = 1;              else                     set countcode = 0;             end if;          end if;          if (oldennamevar != ennamevar)             set countcode = 1;         end if;      if(oldennamevar=null)                     set countcode = 1;     end if;   insert temp_temptable (encodevar,ennamevar,quscorevar,scorerankvar,currathalthletevar,recordcount) values(encodevar,ennamevar,quscorevar,scorerankvar,currathalthletevar,countcode); set oldennamevar = ennamevar;   until done end repeat;  select * temp_temptable;  drop temporary table if exists temp_temptable; close  get_cur;     end

call procedure this: call incrementme();

here's result: enter image description here


Comments

Post a Comment

Popular posts from this blog

jOOQ update returning clause with Oracle -

according http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/update-statement/ jooq doesn't support returning clause in update statements databases except firebird , postgres. does know, still correct oracle jooq? according oracle's documentation, oracle db supports returning clase delete, insert, or update statements. incorrect manual you're right - manual wrong , should fixed: https://github.com/jooq/jooq/issues/5470 single row update returning jooq 3.8 added support single row update .. returning statements. internally, uses callablestatement , pl/sql block of form: begin update "my_table" set "my_table"."column" = 'xyz' "my_table"."id" = 1 returning "my_table"."id", "my_table"."column" ?, ?; ? := sql%rowcount; end; the relevant issue is: https://github.com/jooq/jooq/issues/5190 multi row update returning a...
Read more

java - Warning equals/hashCode on @Data annotation lombok with inheritance -

i have entity inherits other. on other hand, i'm using lombok project reduce boilerplate code, put @data annotation. annotation @data inheritance produces next warning: generating equals/hashcode implementation without call superclass, though class not extend java.lang.object. if intentional, add '@equalsandhashcode(callsuper=false)' type. is advisable add annotation @equalsandhashcode (callsuper = true) or @equalsandhashcode (callsuper = false) ? if not added, 1 callsuper=false or callsuper=true ? the default value false . 1 if don't specify , ignore warning. yes, recommended add @equalsandhashcode annotation on @data annotated classes extend else object. cannot tell if need true or false , depends on class hierarchy, , need examined on case-by-case basis. however, project or package, can configure in lombok.config call super methods if not direct subclass of object. lombok.equalsandhashcode.callsuper = call for configuration system ...
Read more

java - BasicPathUsageException: Cannot join to attribute of basic type -

i using java 8 jpa (hibernate 5.2.1). have clause works perfectly, until introduce clause make use foreign key table values too. i getting following error: basicpathusageexception: cannot join attribute of basic type i think problem related fact have join table, , not sure how create join using join table. employee - employee_category - category model (employee.java): @manytomany(cascade = cascadetype.all, fetch=fetchtype.eager) @jointable ( name="employee_category", joincolumns={ @joincolumn(name="emp_id", referencedcolumnname="id") }, inversejoincolumns={ @joincolumn(name="cat_id", referencedcolumnname="id") } ) private set<category> categories; model (category.java): @id private string id; jpa when introduce following code, fails error above (i think problem new piece of code): join<t, category> category = from.join("id"); predicates.add(criteriabuilder.like(category....
Read more