CODEWITHSUNDEEP
mysqlsql
RasMason
RasMason

Reputation: 2212

select the records with the highest amount

Hi trying to get records from a db table according to the highest percentage of game played 

 user_id| game         | CompletePercentage
----------------------------------
 1      | name1        | 90
 2      | name2        | 67
 3      | name4        | 100
 4      | name1        | 50
 1      | name4        | 80
 1      | name3        | 70
 1      | name1        | 60
 2      | name2        | 70
 1      | name1        | 50

So if I want to get the records for user_id 1 for all games played but only highest 

user_id | game         | highestPercent
----------------------------------
 1      | name1        | 90
 1      | name4        | 80
 1      | name3        | 70

I've been trying

SELECT user_id, game , MAX(CompletePercentage) AS highestPercent 
FROM completedscenarios 
GROUP BY game 
HAVING user_id=1

but with only limited success.
Any help is appreciated.

Upvotes: 1

Views: 104

Answers (6)

maxtwoknight
maxtwoknight

Reputation: 5346

You can try a self join like this fiddle:

Just to expand on some other ways you can use this, I've updated the original fiddle to this. Also, I added an extra datapoint for user 4 to show what happened with duplicates.

  • To get the data for one user (original query):
SELECT c1.*
FROM completedscenarios c1
  LEFT JOIN completedscenarios c2 
    ON c1.user_id = c2.user_id 
      AND c1.game = c2.game 
      AND c1.CompletePercentage < c2.completePercentage
WHERE c1.user_id = 1 and c2.user_id is null;
  • To get all users and their best scores per game
SELECT c1.user_id, c1.game, c1.CompletePercentage
FROM completedscenarios c1
  LEFT JOIN completedscenarios c2 ON c1.user_id = c2.user_id 
    AND c1.game = c2.game 
    AND c1.CompletePercentage < c2.completePercentage
WHERE c2.user_id is null
ORDER BY c1.user_id, c1.CompletePercentage;
  • To get all games and the highest percentages no matter what user
SELECT c1.game, c1.user_id, c1.CompletePercentage
FROM completedscenarios c1
  LEFT JOIN completedscenarios c2 ON c1.game = c2.game 
    AND c1.CompletePercentage < c2.completePercentage
WHERE c2.game is null
ORDER BY c1.game, c1.CompletePercentage;
  • To get users and their best overall game
SELECT c1.user_id, c1.game, c1.CompletePercentage
FROM completedscenarios c1
  LEFT JOIN completedscenarios c2 ON c1.user_id = c2.user_id 
    AND c1.CompletePercentage < c2.completePercentage
WHERE c2.user_id is null
ORDER BY c1.user_id, c1.CompletePercentage;
  • And here is a copy of the results 
USER_ID | GAME  | COMPLETEPERCENTAGE
------------------------------------
1       | name1 | 90
1       | name4 | 80
1       | name3 | 70    

USER_ID | GAME  | COMPLETEPERCENTAGE
------------------------------------
1       | name3 | 70
1       | name4 | 80
1       | name1 | 90
2       | name2 | 70
3       | name4 | 100
4       | name1 | 50
4       | name2 | 70

GAME  | USER_ID | COMPLETEPERCENTAGE
------------------------------------
name1 | 1       | 90
name2 | 2       | 70
name2 | 4       | 70
name3 | 1       | 70
name4 | 3       | 100

USER_ID | GAME  | COMPLETEPERCENTAGE
------------------------------------
1       | name1 | 90
2       | name2 | 70
3       | name4 | 100
4       | name2 | 70

Upvotes: 2

Jacobian
Jacobian

Reputation: 10802

And this query will help you to get the game with the highest percentage per user

SELECT g.user_id, g.game, g.CompletePercentage FROM 
games g 
INNER JOIN (
 SELECT user_id, game, CompletePercentage FROM games
 ORDER BY CompletePercentage DESC
) j ON g.user_id = j.user_id 
GROUP BY g.user_id

It's a well known method of doing ordering before grouping.

Upvotes: 0

Schleis
Schleis

Reputation: 43690

SELECT user_id, game, CompletePercentage as highestPercentage
FROM completedscenarios 
WHERE user_id = 1 
ORDER BY CompletePercentage DESC
LIMIT 3

Order by the percentage, MAX only gives the one maximum value. Since you want more than one record order by the column that you want and use LIMIT to trim the set.

Upvotes: 2

Stuart1044
Stuart1044

Reputation: 444

Group by the user_id and then the game, with a max(percentage)

select user_id, game,max(completepercentage) from @table
where user_id = 1
group by user_id,game

You can then add ordering if you like, but the above gives the desired result

Upvotes: 0

basdwarf
basdwarf

Reputation: 422

SELECT  user_id,
        game,
        MAX(CompletePercentage) AS highestPercent
  FROM completedscenarios
  GROUP BY user_id, game
  WHERE user_id=1

Upvotes: 1

Nicholas King
Nicholas King

Reputation: 938

 SELECT user_id, game,CompletePercentage as highestPercent 
 FROM completedscenarios 
 WHERE user_id=1 
 ORDER BY highestPercent DESC
 Limit 5`

Upvotes: 0

Related Questions