Select all columns, only taking into account the highest scores per user

Question

It's been asked before, but I can't get it to work properly. The selected answer doesn't work with duplicate values. The second answer should be able to handle duplicates according to the poster, but it's not functioning correctly with my data.

What I want to achieve is pretty simple:

I have a database containing all scores of all users. I want to build a highscore table, so I want to select all highscore rows of each user. With highscore row I mean the row for that user where his score is the highest.

Here's a demo I made based on the answer I mentioned at the top:

CREATE TABLE test(
  score INTEGER,
  user_id INTEGER,
  info INTEGER
);

insert into test(score, user_id, info)
values
(1000, 1, 1),
(1000, 1, 2),
(2000, 2, 3),
(2001, 2, 1);

--

SELECT t.*
FROM test t
JOIN (SELECT test.user_id, max(score) as mi FROM test GROUP BY user_id) j ON
  t.score = j.mi AND
  t.user_id  = j.user_id
ORDER BY score DESC, info ASC;

Expected output:

+-------+---------+------+
| score | user_id | info |
+-------+---------+------+
|  2001 |       2 |    1 |
|  1000 |       1 |    1 |
+-------+---------+------+

--> every user_id is present with the row where the user had the highest score value.

Real output:

+-------+---------+------+
| score | user_id | info |
+-------+---------+------+
|  2001 |       2 |    1 |
|  1000 |       1 |    1 |
|  1000 |       1 |    2 |
+-------+---------+------+

--> when there are duplicate values, user show up multiple times.

Anyone who can point me in the right direction?

What is your PRIMARY KEY? – Strawberry Nov 05 '19 at 17:51 — Strawberry, Nov 05 '19 at 17:51

forpas · Accepted Answer · 2019-11-05T17:57:34.820

I assume when there are duplicate scores you want the lowest info just like your expected output.
With NOT EXISTS:

select t.* from test t
where not exists (
  select 1 from test
  where user_id = t.user_id and (
    score > t.score or (score = t.score and info < t.info)
  )  
);

See the demo.
For MySql 8.0+ you can use ROW_NUMBER():

select t.score, t.user_id, t.info
from (
  select *, row_number() over (partition by user_id order by score desc, info asc) rn 
  from test
) t
where t.rn = 1

See the demo.

Results:

| score | user_id | info |
| ----- | ------- | ---- |
| 1000  | 1       | 1    |
| 2001  | 2       | 1    |

score 0 · Answer 2 · answered Nov 05 '19 at 17:58

If the combination of (user_id, info) is UNIQUE and NOT NULL (or PRIMARY KEY), then you can use a LIMIT 1 subquery in the WHERE clause:

SELECT t.*
FROM test t
WHERE (t.score, t.info) = (
  SELECT t2.score, t2.info
  FROM test t2
  WHERE t2.user_id = t.user_id
  ORDER BY t2.score DESC, t2.info ASC
  LIMIT 1
)
ORDER BY t.score DESC, t.info ASC;

The result will be:

| score | user_id | info |
|-------|---------|------|
|  2001 |       2 |    1 |
|  1000 |       1 |    1 |

demo on sqlfiddle

score 0 · Answer 3 · answered Nov 05 '19 at 18:42

SELECT info FROM test HAVING MAX(score) was used to keep the info field relevant with the row containing the MAX(score).

SELECT MAX(score) score, user_id, (SELECT info FROM test HAVING MAX(score)) AS info FROM test GROUP BY user_id ORDER BY score DESC;

Select all columns, only taking into account the highest scores per user

3 Answers3