Databases
StackOverflow2013
Postsdbo
POEfficient way to SELECT a fixed range of high scores given only a user id
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEfficient way to SELECT a fixed range of high scores given only a user id
    primarykey
    data
    text
    <p>I have a table consisting of records of every match that every player has played. I've gotten a pretty good head start from the top answer of this question: <a href="https://stackoverflow.com/questions/5436263/ranking-with-millions-of-entries">Ranking with millions of entries</a>, and am currently using this query to retrieve each player's highest score and their rank:</p> <pre><code>SET @rank=0; SELECT user_id, score, rank FROM ( SELECT *, @rank := @rank + 1 as rank FROM high_scores JOIN ( SELECT user_id as user_id2, MAX(score) AS max_score FROM high_scores GROUP BY user_id2 ORDER BY max_score DESC) AS max_score_table ON max_score = high_scores.score AND high_scores.user_id = user_id2) derived_table ORDER BY rank; </code></pre> <p>Again, this gives me a nice ordered list of each player's top score and its rank; However, I'd also like to be able to supply a specific <code>user_id</code> and filter the results down to this user's score as well as X amount of surrounding higher and lower scores. </p> <p>I would think I'd need to perform a <code>SELECT ... WHERE</code> on "derived_table" for the <code>user id</code> and use the returned row's <code>'rank'</code> to filter the top-level <code>SELECT</code> statement, but in addition to the query not even being accepted ('derived_table doesn't exist'), the way I was doing it would have required me to re-query derived_table twice (for a greater-than and less-than test), making the query even less efficient than it probably should be.</p> <p>How can I filter the full list of high scores and ranks down to a single <code>user_id</code>'s entry and X amount of surrounding entries? Any insight on the code I'm trying to come up with (or the code I currently have) would be greatly appreciated.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJohnny Cage
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POEfficient way to SELECT a fixed range of high scores given only a user id
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POEfficient way to SELECT a fixed range of high scores given only a user id
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POEfficient way to SELECT a fixed range of high scores given only a user id
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload