28519

most simple “return highscores” SQL Query using userID

Question:

High there

working at a minimal game on Android I get lost when it comes to sql queries.

I'm trying to get a top 100 ranking list and it almost works but not quite

I am using this query

$sql = "SELECT * FROM WorldGames_table GROUP BY user_id ORDER BY score DESC LIMIT 100";

the thing is that it returns a list of the top 100 scores, in the proper order and containing only 1 score of each user. But it is not always the best score of the users that is shown.

I am getting completely confused by the way complexes queries are "organised", that is why I am looking for a simple query

cheers

<strong>UPDATE</strong>: Solved (or so I though at the time :s)

this is the line that returns what i was looking for :D

SELECT WorldGames_table .*, MAX(score) as score FROM WorldGames_table GROUP BY id, device_id ORDER BY score DESC LIMIT 100;

Thanks to AT-2016 and to every one who gave a hand :D

<strong>UPDATE II</strong>: not solved after all :S

So happy that the proper Highscores were showing up in the ranking, I didn't realise that, actually, with this query <em>All the scores from each user are showing up</em>, instead of returning only the best score of each user. Here I go again, in the search of the most simple "return highscores" SQL Query using userID

<strong>UPDATE III</strong>: I think the answer might come from <a href="https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column" rel="nofollow">this place</a> so I put this together, and again, it works in my local test and fails when a apply it on my server

SELECT a.id, a.timestamp, a.name, a.score, a.color, a.flower, a.user_id FROM games_table a INNER JOIN ( SELECT id, MAX(score) score FROM games_table GROUP BY user_id ) b ON a.id = b.id AND a.score = b.score ORDER BY a.score DESC LIMIT 100

Still investigating, the piñata way ;)

<strong>UPDATE IV</strong>: I think I have it (but I won't it is done before I'm sur) In the mean time this is what I found. NOPE, ANOTHER FAILED ATTEMPT :(

I tried my queries directly inside PHPmyAdmin

Found out that that query

SELECT timestamp, name, MAX(score), color, flower, device_id FROM WorldFlowers_table GROUP BY device_id ORDER BY score DESC LIMIT 100;

returned an error message like this: "Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available."

So I added "id," at the beginning of line #2

and now it looks like I get the result I was looking for I did some test but I will make sur it works to mark the question as "answered"

Did not work, now I'm looking at <a href="https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column?rq=1" rel="nofollow">this thread</a> without much hope

The only thing I gained today is that I test the queries directly into phpmyadmin

if there is anyone out there <strong>HELP !</strong>

<strong>UPDATE V</strong>: someone called scaisEdge gave me this answer and, at least until now, it seems to work. Waiting for more testing to say "answered"

select * from WorldFlowers_table where (device_id, score) in ( select device_id, max(score) FROM WorldFlowers_table GROUP BY device_id ORDER BY score DESC ) ORDER BY score DESC LIMIT 100

<strong>UPDATE VI</strong>

it works !! :)

and the answer was given to me , like I said above, by scaisEdge, in another "instance" of this question that I posted here <a href="https://stackoverflow.com/questions/39833606/mysql-get-top-100-scores-query-is-turning-me-crazy" rel="nofollow">mySQL "get TOP 100 scores" query is turning me crazy</a>

Answer1:

Try to use MAX() to get the highest values:

SELECT MAX(Values) FROM WorldGames_table GROUP BY user_id ORDER BY score DESC LIMIT 100

Try the below:

SELECT * FROM Table JOIN (SELECT MIN(Score) as Values -- Or use MAX() FROM (SELECT Score FROM Table ORDER BY Score LIMIT 100 ) Table2 ) Table2 ON Table.Score >= Table2;

Answer2:

Have you tried to use the sum?

$sql = "SELECT SUM(score) as sum_score FROM WorldGames_table GROUP BY user_id ORDER BY SUM(score) DESC LIMIT 100";

Answer3:

I don't know if I understand you well but maybe you need to write your query like that:

$sql = "SELECT WorldGames_table.*, MAX(score) as score FROM WorldGames_table GROUP BY user_id ORDER BY score DESC LIMIT 100";

Recommend

  • Blitting text in pygame
  • NSMutableArray writeToUrl
  • Getting one row sql
  • Simple way to keep track of a high score with corona sdk [closed]
  • Best Practices for Building a SSO System
  • Keycloak: How to auto redirect Keycloak user to OKTA SSO page instead of clicking on button?
  • Share a login cookie/session between domains in Rails 5
  • create-react-app: Change src of bundle.js in development
  • Can I deploy Python .pyc files only to Google App Engine?
  • Core Data vs SQLite [closed]
  • What is the fastest way to select nearest geographical place from mysql database?
  • Reverse output of polyfit numpy
  • GridBagLayout padding
  • Change behaviour of Print button in ReportViewer C#
  • What is wrong with this emulation of CMPXCHG16B instruction?
  • Waiting for $.post to finish
  • jsTree : Append child node dynamically
  • Publishing HTML Field Control Converts Relative URL to Absolute URL
  • get latest text entry of GROUP BY statement
  • Django Migrations fail during django initialization
  • wrong item changes in recyclerview
  • Storing a copy of a document embedded in another document in MongoDB via Mongoose
  • DateTime differences between rows in a database
  • Select column where another related column's total is 0
  • Installing SSL on AWS EC2 Bitnami Mean Stack
  • css font-size and line-height not matching the baseline
  • Typecasting `this` of a base class template to its derived class
  • how do you obtain the address of an instance after overriding the __str__ method in python
  • Access the state of control in Winforms from another application
  • Certain Arabic text gets incorrectly shown while other Arabic text gets showed normally?
  • How do I Dispose a HttpResponseMessage in my Web Api Method?
  • How to implement Deep Linking in Roku SG application?
  • How to make R's read_csv2() recognise the text characters properly
  • Button text different than value submitted in query string
  • Inline R code in YAML for rmarkdown doesn't run
  • How can the INSERT … ON CONFLICT (id) DO UPDATE… syntax be used with a sequence ID?
  • WPF - CanExecute dosn't fire when raising Commands from a UserControl
  • ActiveRecord query for a count of new users by day
  • Knitr HTML Loop - Some HTML output, some R output
  • C# - Getting references of reference