mysql - Combining between two queries -


i have 2 big queries, individually both work , produce want.

query 1:

select pt.map, pt.time, pt.jumps, pt.id playertimes pt auth = '[u:1:204506329]' , style = 0 order map;

(in action, [u:1:204506329] variable)

query 2:

select count(*) rank playertimes pt left join users u on u.auth = pt.auth pt.time <= (     select time     playertimes     map = 'bhop_horsepoop_8' , style = 0 , auth = '[u:1:204506329]'     limit 1 ) , pt.map = 'bhop_horsepoop_8' , pt.style = 0 order time asc limit 1;

(again, [u:1:204506329] , bhop_horsepoop_8 variables)

the purpose of query 1 retrieve list of maps, times, jumps , ids auth define.

the purpose of query 2, find auth's position ('rank') in playertimes table on defined map , style. intention, sum both of single query. receive both want in first query, , rank field every row retrieved in query 1.

this tried:

select pt.map, pt.time, pt.jumps, pt.id, (     select count(*) rank     playertimes pt     left join users u on u.auth = pt.auth     pt.time <= (         select time         playertimes         map = pt.map , style = 0 , auth = '[u:1:204506329]'         limit 1     ) , pt.map = map , pt.style = 0 order time asc limit 1 ) rank playertimes pt auth = '[u:1:204506329]' , style = 0 order map;

the results ended being incorrect!

the following resultset of query (phpmyadmin):

query resultset

as see, rank same , should change every row. while want rank calculated according map of same row.

i'm stuck. (and explanation, if possible) appreciated!

update: have tried mike's solution, query ended with:

select * (   select pt.map, pt.time, pt.jumps, pt.id, pt.auth     playertimes pt         auth = '[u:1:204506329]' , style = 0             order map         )     left join     (         select count(*) rank             playertimes pt                 left join users u                     on u.auth = pt.auth                         pt.time <=(                             select time, auth                                 playertimes                                     map = a.map , style = 0 , auth = '[u:1:204506329]'                                         limit 1   )         , pt.map = a.map , pt.style = 0             order time asc                 limit 1                     ) b     on a.auth = b.auth;

the following error mariadb returning:

 #1054 - unknown column 'a.map' in 'where clause'

i'm not sure how fix one. but, tried filling where a.map constant string, output:

 #1241 - operand should contain 1 column(s)

which of course because tried select both time , auth operand (while i'm supposed select 1 field), have otherwise on a.auth = b.auth throw error b.auth being unknown column in on clause.

update 2: have come partial solution, query works rank applies map define.

select a.map,        a.time,        a.jumps,        a.id,        a.auth,        b.rank   ( select pt.map,            pt.time,            pt.jumps,            pt.id,            pt.auth    playertimes pt    auth = '[u:1:204506329]'      , style = 0    order map ) left join   ( select count(*) rank,            pt.auth    playertimes pt    left join users u on u.auth = pt.auth    pt.time <=        ( select time         playertimes         map = 'bhop_horsepoop_8'           , style = 0           , auth = '[u:1:204506329]' limit 1 )      , pt.map = 'bhop_horsepoop_8'      , pt.style = 0    order time asc limit 1 ) b on a.auth = b.auth;

i couldn't think of solution calculate rank current row.


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