1

Here are my tables

+----------+-----------+
|   id     |  user_id  |
+----------+-----------+
|    1     |     1     |
+----------+-----------+
|    2     |     1     |
+----------+-----------+
|    3     |     1     |
+----------+-----------+
|    4     |     2     |
+----------+-----------+
|    5     |     2     |
+----------+-----------+
|    6     |     2     |
+----------+-----------+
|    7     |     3     |
+----------+-----------+
|    8     |     3     |
+----------+-----------+
|    9     |     3     |
+----------+-----------+

My second table

+----------+---------+
|   id     |  score  |
+----------+---------+
|    1     |    10   |
+----------+---------+
|    2     |    20   |
+----------+---------+
|    3     |     5   |
+----------+---------+
|    4     |    40   |
+----------+---------+
|    5     |    15   |
+----------+---------+
|    6     |    10   |
+----------+---------+
|    7     |     5   |
+----------+---------+
|    8     |    30   |
+----------+---------+
|    9     |    10   |
+----------+---------+

I need to select the highest score achieved by a user from these tables.

Here is my MySql query

SELECT * FROM 
table_1 AS t1
INNER JOIN 
table_2 AS t2 ON 
t1.id = t2.id 
WHERE t2.score > 10
GROUP BY t1.user_id
ORDER BY t2.score DESC

My desire result is

+----------+-----------+---------+
|   id     |  user_id  |  score  |
+----------+-----------+---------+
|    4     |      2    |    40   |
+----------+-----------+---------+
|    8     |      3    |    30   |
+----------+-----------+---------+
|    2     |      1    |    20   |
+----------+-----------+---------+

But what I get is

+----------+-----------+---------+
|   id     |  user_id  |  score  |
+----------+-----------+---------+
|    4     |      2    |    40   |
+----------+-----------+---------+
|    1     |      1    |    10   |
+----------+-----------+---------+
|    7     |      3    |     5   |
+----------+-----------+---------+

MySql is always selecting the lowest id from table_1 when I'm using the GROUP BY clause

I tried using the MAX command like this

SELECT *, MAX(t2.score) AS max_score FROM 
table_1 AS t1
INNER JOIN 
table_2 AS t2 ON 
t1.id = t2.id 
WHERE t2.score > 10
GROUP BY t1.user_id
ORDER BY t2.score DESC
LIMIT 10

And the result I'm getting

+----------+-----------+---------+-----------+
|   id     |  user_id  |  score  | max_score |
+----------+-----------+---------+-----------+
|    4     |      2    |    40   |    40     |
+----------+-----------+---------+-----------+
|    1     |      1    |    10   |    20     |
+----------+-----------+---------+-----------+
|    7     |      3    |     5   |    30     |
+----------+-----------+---------+-----------+

I believe the result I wish it quite easy to get but I'm nowhere there.

Update 1

This question was marked duplicate but unfortunately I couldn't find any solution on that given page.

Here is the query that I'm trying but it fails.

SELECT * AS max_score FROM 
table_1 AS t1
INNER JOIN 
(
SELECT *, MAX(score) AS max_score
FROM table_2
GROUP BY t1.user_id
) AS t2
ON 
t1.id = t2.id 
WHERE t2.score > 10
ORDER BY t2.score DESC
LIMIT 10

It gives me the error Unknown column t1.user_id

I'm trying to get the highest value from the column score which is in table_2 and group the results by user_id which is in table_1.

The examples given on those pages target only one table and I'cant make that work on my scenario.

Anuj
  • 333
  • 4
  • 14
  • @Barmar would you like to re check the question and remove the duplicate tag from this or redirect to some another Q/A which represent the same kind of scenario? – Anuj Sep 10 '16 at 05:53
  • Did I misunderstand the question? After you join the two tables, you want the row with the highest score in each user_id group. The other question shows how to get the row with the max of a column in each group. – Barmar Sep 10 '16 at 06:08
  • Yes @barmar I exactly need the highest score for each user group. My columns score (applying MAX here) and user id (applying GROUP BY here) are on 2 different table. It would be great if you can redirect me to some pages with same kind of situation. – Anuj Sep 10 '16 at 06:39
  • Then what's the problem with the duplicate? It's about finding the row with the max value of a column in each group. The solutions are the same whether you're getting it from a single table or joining two tables. – Barmar Sep 10 '16 at 06:45
  • I know what you are saying is right but somehow I'm not just able to implement the solution on my own. If you don't mind have time then could you please write the exact code snippet. – Anuj Sep 10 '16 at 06:48
  • It seems like you need to get yourself a programmer. If you're unable to generalize from answers like that, you're going to have a hard time doing this stuff on your own. – Barmar Sep 10 '16 at 06:51

1 Answers1

2

Write a subquery that gets the max score for each user_id. Then join that with your tables to get the rows with that max score.

SELECT t1.id, t1.user_id, max_score
FROM table_1 AS t1
JOIN table_2 AS t2 ON t1.id = t2.id
JOIN (
    SELECT t1.user_id, MAX(t2.score) AS max_score
    FROM table_1 AS t1
    JOIN table_2 AS t2 on t1.id = t2.id
    GROUP BY t1.user_id) AS t_max
ON t1.user_id = t_max.user_id AND t2.score = t_max.max_score

DEMO

Barmar
  • 741,623
  • 53
  • 500
  • 612