Mysql - Fetch 5 rows above and under user (ranking) -


i have table: 

r_user  | r_points | -------------------- user1   | 12       | user2   | 124      | user3   | 34       | user4   | 35       | user5   | 57       | user6   | 3        | user7   | 452      | user8   | 8        | user9   | 12       | user-c  | 0        | user-b  | 0        | user-a  | 0        |

lets want fetch ranking user6. want 5 users above , below entry ordered r_points , next r_user asc, users equal points ( user-c, user-b, user-a ) sorted asc ( user-a, user-b... ).

if there aren't 5 users above or underneath, difference should put opposite. if user6 4 bottom, want 6 users fetched above. count of 10 users.

that result, have:

r_user  | r_points | -------------------- user5   | 57       | user4   | 35       | user3   | 34       | user9   | 12       | user1   | 12       | -> in direction order other way round user8   | 8        | user6   | 3        | --> thats user id <-- user-a  | 0        | user-b  | 0        | user-c  | 0        |

i have no idea, how that, since table has r_user pk , no ai-id.

thank you

edit:

that's version of it. version not fetching more users above/below if user on 3rd bottom. works if there 5 rows underneath , above. that's not want.

( select r_user, r_points ranking  r_points >= ( select r_points ranking r_user = 'user6' ) order r_points asc limit 5 ) union distinct ( select r_user, r_points ranking  r_points <= ( select r_points ranking r_user = 'user6' ) order r_points desc limit 6 ) order r_points desc, r_user asc

you wrote have no unique id, in fiddle user hard coded line if(t.r_user = 'user2'. change user have change value.

hope helpful you:

http://sqlfiddle.com/#!9/d2329/31

select * (   select      t.*,     if(@idx null, @idx:=1, @idx:=@idx+1) idx,     if(t.r_user = 'user2',@median:=r_points, if(@start null, @start:=@idx, if(@after null, @start:=@idx, null))),     if(@median null, if(@before null,@before:=1, @before:=@before+1) ,     if(@after null ,@after:=0, @after:=@after+1))     (     select *      table1      order r_points desc     ) t   ) t2  if(1+@before+@after<=10, true,           if(@before<=5, t2.idx >= 0 , t2.idx <= @start+5+(5-@before),              if(@after<=4, t2.idx >= @start-4-(4-@after),                 t2.idx >= @start-4 , t2.idx <= @start+5              )           )         );

Comments

Post a Comment

Popular posts from this blog

shopping cart - Page redirect not working PHP -

im trying redirect user when click "empty cart". redirect working "add cart" not "empty cart". ideas great. in main .php file set current url seen $current_url = base64_encode("http://$_server[http_host]$_server[request_uri]"); then below cartaction.php file handles actions such "add cart" , "empty cart" <?php session_start(); include_once 'config.php'; //add item in shopping cart if(isset($_post["add"])) { $product_id = $_post["product_id"]; //product id $return_url = base64_decode($_post["return_url"]); //return url $qty = $_post["qty"]; $product = $mysqli->query("select product_name, unit_price, unit_quantity products product_id = '$product_id'"); $obj = $product->fetch_object(); if(!isset($_session['products'])) { $_session['products'] = array(); } if(array_key_exists($product_id...
Read more

php - How to modify a menu to show sub-menus -

i have custom php menu made person, displays categories menu. however, need display subcategories well. tried adding hierarchical parameter didn't work. hoping assistance! here code: <ul> <?php /* categories wp e-commerce products */ $wpec_product_categories = get_terms( 'wpsc_product_category', 'hide_empty=0&parent=0&exclude=298'); $myarray = array(); foreach($wpec_product_categories $wpec_categories){ $myarray[$wpec_categories->term_id]['name'] = $wpec_categories->name; $myarray[$wpec_categories->term_id]['slug'] = $wpec_categories->slug; $myarray[$wpec_categories->term_id]['term_id'] = $wpec_categories->term_id; } foreach($myarray $wpec_categories1){ $wpec_term_name = $wpec_categories1['name']; $wpec_term_slug = $wpec_categories1['slug']; $wpec_term_id = $wpec_categories1['term_id']; echo '<li> <a href="'.get_page_link(19)....
Read more

python - Installing PyDev in eclipse is failed -

unable read repository @ http://pydev.sf.net/updates/content.xml. sun.security.validator.validatorexception: pkix path building failed: sun.security.provider.certpath.suncertpathbuilderexception: unable find valid certification path requested target i installing pydev in eclipse using python, doesn't install successfully. gives above error. happened? how solve error? i had error when trying install on laptop behind corporate firewall. couldn't tell setup of firewall, not sure of root cause. i got passed error installing pydev eclipse when out behind firewall. another way got around have eclipse on non-firewalled machine, install pydev eclipse, , copy whole eclipse directory non-firewalled machine firewalled machine. there better way, , way fix properly, didn't have time work out firewall doing. this on windows 7 machine (yeah know) not work *nix versions installed through package manager yum or apt.
Read more