CODEWITHSUNDEEP
mysqlsql
user962449
user962449

Reputation: 3873

Selecting distinct values in a mysql table

Here's my mysql table:

Table League (userid and lid are primary keys):

*userid*  *lid*     rank    win     loss     streak  score
---------------------------------------------------------
    2       1        1       2       0         2      10
    2       3        2       1       1         1       5
    5       1        2       1       1         1       5

I'm trying to select the users with the top score only once. For example since userid 2 is in league (lid) 1 and 3, only his top score will be selected in the query. So in that case the row with score 10 would be selected since that's the users top score from both league 1 and 3. The row with lid 3 will not be selected.

So the query results should look like this:

userid  lid     rank    win     loss     streak  score
---------------------------------------------------------
  2      1       1       2       0         2      10
  5      1       2       1       1         1       5

As you can see userid 2 with lid 3 was not in the result because the score 10 from lid 1 was grater than score 5 from league 3. Any ideas?

Upvotes: 2

Views: 128

Answers (3)

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

SELECT l.userid, u.username, l.lid, l.rank, l.win, l.loss, l.streak, l.score
    FROM (SELECT userid, MAX(score) AS MaxScore
              FROM League 
              GROUP BY userid) q
        INNER JOIN League l
            ON q.userid = l.userid
                AND q.MaxScore = l.score
        INNER JOIN users u
            ON l.userid = u.userid

Upvotes: 2

dani herrera
dani herrera

Reputation: 51665

Here the simplest solution:

SELECT *
FROM League t1 
WHERE 
  t1.lig = (SELECT t2.lig
        FROM League t2
        WHERE t2.userid = t1.userid
        ORDER BY score desc
        LIMIT 1
     )

Upvotes: 0

Jean-Bernard Pellerin
Jean-Bernard Pellerin

Reputation: 12670

SELECT *
FROM table t1
WHERE t1.score = (SELECT MAX(t2.score)
  FROM table t2
  WHERE t1.userid = t2.userid)

This will display ties, don't know if you want those or not.

Upvotes: 0

Related Questions