how to stop a query once a certain row is reached

[Shadowing]

Having a issue on figuring how to do this. I have a table with a unique I'd with a int column for a rank system.

 

I need to figure out what rank a player is. Right now I'm selecting the players rank and then doing a count all rows where rank is >= $players_rank

 

I'm using pigmentation links so I'm trying to figure out what page the user would be on. The problem is in a rare situation where more then one player has the same amount of rank and the rank is split up over two pages.

 

Hope I explained this good enough. Its a pickle of a problem

[requinix]

Pagination. It's called pagination. Not pigmentation.

 

You know how many rows you want on a page, right? Use a LIMIT clause to reflect that.

SELECT fields FROM table WHERE rank >= $players_rank ORDER BY rank ASC LIMIT 50 /* 50 rows per page */

[Shadowing]

Thanks for the reply. Lol my tablet auto filled that word lol.

I need to return the position the player is in rank

[Shadowing]

I found this example online but I don't understand it enough to edit it

 

 

SELECT COUNT(*) FROM game ui
     WHERE (ui.points,ui.id >= (uo.points, uo.id)
     ) AS rank
     FROM game uo
     WHERE id = @id

[requinix]

Pretty sure you don't want the ui.id part.

SELECT COUNT(*) + 1 FROM table WHERE points > players points /* and then +1 */
or
SELECT COUNT(*) FROM table WHERE points >= players points

One of those will give you the rank number you want, and it depends on what rank number you would give someone if they were tied for first place. Say three people score A=10, B=10, and C=9. Are the rankings #1, #1, #3 (ties get the highest rank) or #2, #2, #3 (ties get the lowest rank)?

[Shadowing]

ya thats the problem i have is ties. so when I select the persons rank it lines up where they are in the pagination.

 

right now on my pagination script it just pulls in order if there is two users with the same experience points (for example) I'm not sure how mysql orders that if im doing order by experience DESC not sure how mysql decides who is next in line.

 

My first idea was to use

SELECT COUNT(*) FROM table WHERE points > players points

but then i was like oh wait that won't work for ties.

 

So thats the problem im having I need to pull the players exact rank as to where they would show up on my pagination script

 

 

I can do this if i used another select like select all users who have the same experience as that player and then take that informtation to figure out how many players are ahead of that player but that just seem so lame to do that.

 

 

 

I"m working on this new idea now that almost works but for some reason its off by like 4 some how haha no idea why

SET @rownum := 0;
SELECT rank, experience FROM ( 
            SELECT @rownum := @rownum + 1 AS rank, experience, id                   
             FROM game ORDER BY experience DESC                   
) as result WHERE id= 3112

Edited April 18, 2013 by Shadowing

[Shadowing]

wierd i just noticed another problem if I do this in myphpadmin

SELECT experience, id FROM game ORDER BY experience DESC

it gives me a completly differant order then my pagination script which uses

"SELECT experience,id FROM game ORDER BY experience DESC LIMIT $offset, $rowsperpage"

Edited April 18, 2013 by Shadowing

[Shadowing]

Since InnoDB has no real order im starting to think its kinda random cause i just notice if order changes on ties even on my script. Never notice this before probably cause I didnt have so many users having a tie before

[Barand]

There's a query here to get rankings

 

http://forums.phpfreaks.com/topic/269683-give-same-rank-where-points-are-the-same/?do=findComment&comment=1386466

[Shadowing]

Thanks Barand I"m going to try that query out. I did solve the issue just using a 2nd query which was plan B but If that query works then im going to use that instead.

 

I have another issue now though lol

Im trying to do ranks with Alliances in my game.

 

I need to do SUM(game.networth) and compare it to $users_alliance_stat for each alliance. I googled the heck out of this.

This query almost works accept it doesnt count each alliance instead returns how many members are in the first alliance.

What im trying to do is add up all the players networth that is in each alliance so I can figure out what rank a alliance is

 

 
SELECT COUNT(*), SUM(game.networth) as sum_stat FROM game  GROUP BY alliance HAVING sum_stat > $users_alliance_stat

 

 

 

an easier example that doesnt work with sql. But probably easier to understand what i'm trying to do

 

SELECT COUNT(*) FROM game WHERE SUM(game.networth)  > $users_alliance_stat

[Shadowing]

I put together a test table

CREATE TABLE test
 (
 id INT AUTO_INCREMENT PRIMARY KEY,
 alliance int(5),
 networth int(5)
 )

INSERT INTO test (alliance,networth)
VALUES(2,100),(2,150),
(3,130),(3,70),(3,80),
(4,300),(4,200),(4,100),(4,100),
(5,100),(5,220),(5,180),
(6,100),(6,220),(6,110),(6,120),(6,80)

Edited April 18, 2013 by Shadowing

[Barand]

If you are ranking aggregations it's bit more complex

 

http://forums.phpfreaks.com/topic/277023-ranking-counts/?do=findComment&comment=1425232

[Barand]

Using your data

SELECT alliance, rank, total
FROM (
    SELECT alliance, 
    @row := @row+1,
    @rank := IF(totnet=@lasttot, @rank, @row) as rank,
    @lasttot := totnet as total
    FROM 
        (
        SELECT alliance, SUM(networth) as totnet
        FROM alliancetest
        GROUP BY alliance
        ORDER BY SUM(networth) DESC
        ) as tot
        JOIN (SELECT @row:=0, @lasttot:=0,@rank:=0) AS init
    ) ranks

results

+----------+------+-------+
| alliance | rank | total |
+----------+------+-------+
|        5 | 1    |  1360 |
|        4 | 2    |   700 |
|        3 | 3    |   280 |
|        2 | 4    |   250 |
+----------+------+-------+

Edited April 18, 2013 by Barand

[Shadowing]

Ahh thanks so much for doing that Barand i was sitting here trying to fill in my stuff on your other post in that other thread but that guy is using two differant tables

 

My SQL isnt this high in level yet

 

had to change the table to test instead of alliancetest but it works well. Thanks so much

 

hopefully this example will help break me into understanding sql variables and statements with subqueries. Cause i would really like to beable to free hand write sql on that level

Edited April 18, 2013 by Shadowing

[Shadowing]

and all i have to do is just add a WHERE clause to grab a certain alliance

 
SELECT alliance, rank, total
FROM (
    SELECT alliance,
    @row := @row+1,
    @rank := IF(totnet=@lasttot, @rank, @row) as rank,
    @lasttot := totnet as total
    FROM
        (
        SELECT alliance, SUM(networth) as totnet
        FROM test
        GROUP BY alliance
        ORDER BY SUM(networth) DESC
        ) as tot
        JOIN (SELECT @row:=0, @lasttot:=0,@rank:=0) AS init
    ) ranks WHERE alliance = 5
 

works great

Edited April 18, 2013 by Shadowing

[Shadowing]

One thing though. Isnt that pretty heavy weight since its ranking every single alliance even past the rank of a alliance I need.

[Barand]

How would you get a ranking if only calculate totals for some of them?

[Shadowing]

One way is have it start the rank from where the alliance you are searching for. Only start the rank from the networth where your alliance is at all the way to rank 1.

Edited April 19, 2013 by Shadowing