andy75180 Posted September 11, 2007 Share Posted September 11, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/68926-solved-mysql-chess-league-table-extraction/ Share on other sites More sharing options...
liebs19 Posted September 11, 2007 Share Posted September 11, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/68926-solved-mysql-chess-league-table-extraction/#findComment-346472 Share on other sites More sharing options...
andy75180 Posted September 12, 2007 Author Share Posted September 12, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/68926-solved-mysql-chess-league-table-extraction/#findComment-346765 Share on other sites More sharing options...
andy75180 Posted September 14, 2007 Author Share Posted September 14, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/68926-solved-mysql-chess-league-table-extraction/#findComment-348224 Share on other sites More sharing options...
fenway Posted September 15, 2007 Share Posted September 15, 2007 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). Quote Link to comment https://forums.phpfreaks.com/topic/68926-solved-mysql-chess-league-table-extraction/#findComment-349033 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.