[SOLVED] Mysql Chess League Table Extraction

[andy75180]

Hi ya,

 

Any help with this problem anyone?

 

Have decided to include a top 10 chess league position table on my homepage. The league is sorted by the number of points a player has. 1st place the most points. This has been an easy task to do, using ORDER BY and LIMIT mysql commands.

 

Problem is I run into problems when 2 or more players have the same league points. I've decided that players will share the position when this happens, but mysql will only return the top 10. Obviously if 2 players have 10th position I need to extract 11 players from the database.

 

I could do this retrieving all players and sorting the list using PHP array. Quite easy, but is this the most efficent to go about it? Extra time retrieving rows from database I won't be using.

 

My questions I'm asking is; is it possible to retrieve the top 10 players with the highest score, but include more players if league points are the same.

 

Hope I've explain myself properly. To visually see my chess games league table may help solve this problem.

 

Any help most appricated.

[liebs19]

Why not retrieve the highest 10 scores in a query first, and then retrieve player records based off of that data?

 

SELECT * FROM tblPlayers where player_score >= (SELECT DISTINCT score FROM player_score ORDER BY score DESC LIMIT 9,1)

 

My SQL is a bit rusty but I believe that query will get you all the records where the score is greater then or equal to the 10th highest score. The DISTINCT will only get each score once, the ORDER BY score DESC will start it with the highest score, and the LIMIT 9,1 will start at the 9th record and bring back the 10th.

[andy75180]

Mmmm, sounds a better idea. Haven't got time to try it at the moment, but will let you know how I get on. Thankyou for replying.

[andy75180]

Your SQL isn't rusty at all. Yes I've tried your idea and my league table is working perfect now. Thankyou very much for time solving my problem.

[fenway]

SELECT * FROM tblPlayers where player_score >= (SELECT DISTINCT score FROM player_score ORDER BY score DESC LIMIT 9,1)

 

FYI, this isn't as effecient as using a dervied table with this information, since it doesn't depend on the player_score value (so a subquery is expensive for no reason).