Select minimum of the column from another table

Question

I have two tables:

score_table with columns player_id and score, where player_id is unique

group_table with columns player_id and group_id, where player_id is unique.

There is ~1000 entries in both tables, and two different values in group_table.group_id

     score_table                 group_table
 ____________________       ____________________       
| player_id    score |     |player_id   group_id|
|                    |     |                    |  
|   0           200  |     |   1            0   |
|   1            1   |     |   3            1   |
|   4            0   |     |   2            0   |
|   3           114  |     |   0            0   |
|   2            9   |     |   4            1   |
|  ...          ...  |     |  ...          ...  |
|  ...          ...  |     |  ...          ...  |

I would like to select the player with the smallest score, but it must be in group_id 0

Base on this answer, the query which I've come up with:

SELECT player_id FROM (

      SELECT MIN(st.score) 
      FROM score_table st

      INNER JOIN  group_table gt
      ON  gt.player_id = st.player_id

      WHERE  gt.group_id = 0
      ORDER BY  st.score ASC
     )"

However, the query is constructed incorrectly, and the result is always null

Edit:

simple " SELECT MAX(st.score) as score FROM score_table pg" does return 44, which is the correct, highest score. I've tested all the entries using similar queries and each one is retrievable. Both tables are using BTREE for player_id, which is unique in both cases.

Edit:

"SELECT * FROM score_table as st JOIN group_table gt ON pg.player_id = gt.player_id"

followed by var_dump outputs an arary of N-entries, where each entry contains columns from both tables, joined together, correctly

Answer Accepted

So the issue was in the alias of table names. When we SELECT MIN(st.score) the result will not be accessible with fetch(PDO::FETCH_NUMERIC)['score']

I had to use ['MIN(st.score)'] as the key.

Your query select smallest score for group_id = 0. And I think that you don't understand basics of sql... — GrApDev, Dec 14 '16 at 13:05
Sorry I didn't understand what you wrote. The idea is to return the highest score from the players, but only considering those players who are in group 0. But somehow that returns null :/ — Kari, Dec 14 '16 at 14:06

score 1 · Accepted Answer · answered Dec 14 '16 at 12:16

1

Your query has an error, because the subquery has no alias. You should learn how to check errors, however you are calling the query.

But I think you want:

  SELECT st.* 
  FROM score_table st INNER JOIN
       group_table gt
       ON gt.player_id = st.player_id
  WHERE  gt.group_id = 0
  ORDER BY st.score ASC
  LIMIT 1;

You specify that you want to select the player with the smallest score, not the smallest score. The above returns everything in the score_table, but you can select any columns you want.

answered Dec 14 '16 at 12:16

Gordon Linoff

1,242,037
58
646
786

The query runs fine, but produces no output. Doing usual prepare on the pdo, running execute on the returned PDOstatement, getting fetch from it as numeric array, 0th index. But getting null from its vardump. All the names of tables / their columns are correct, and there are columns to select from – Kari Dec 14 '16 at 12:37
@user7296262 . . . The query in your question is missing an alias on the derived table. MySQL requires table aliases in that situation. – Gordon Linoff Dec 14 '16 at 13:17
Thank you Gordon; It indeed was the issue with aliases. Is it the most efficient way to get the player with the lowest score within this group (might be ran in parallel by 100 000 clients). What is the benefit of Limit vs Min? – – Kari Dec 14 '16 at 15:17
1

@user7296262 . . . Your question explicitly states "I would like to select the player with the smallest score". The `order by`/`limit` gives you the player. The `min()` does not. – Gordon Linoff Dec 14 '16 at 23:56

Rahul · Answer 2 · 2016-12-14T12:49:59.870

0

Try changing your query like

SELECT MIN(st.score) 
FROM score_table st
      LEFT JOIN  group_table gt
      ON  gt.player_id = st.player_id
      AND  gt.group_id = 0;

edited Dec 14 '16 at 12:49

answered Dec 14 '16 at 12:13

Rahul

76,197
13
71
125

query runs, but the output is null – Kari Dec 14 '16 at 12:29
@user7296262, try the edited query in answer and see if it makes difference – Rahul Dec 14 '16 at 12:50
vardump of this statements results in (1) { [0]=> NULL } for me. However, simple " SELECT MAX(st.score) as score FROM score_table pg" does return 44, which is the correct, highest score. I've tested all the entries using similar queries and each one is retrievable. Both tables are using BTREE for player_id, which is unique in both cases – Kari Dec 14 '16 at 14:03

score 0 · Answer 3 · answered Dec 14 '16 at 12:19

You can use the following query if you want to get multiple players with minimum score

select player_id from score_table where score = (select min(score) from score_table st join group_table gt on st.player_id =gt.group_id and gt.group_id = 0)

If you want to get only one player, you can add Limit 1 to the main query.

Ravi Matani · Answer 4 · 2016-12-14T12:59:44.780

0

Try below query:

SELECT st.player_id
FROM score_table st
LEFT JOIN  group_table gt
ON  gt.player_id = st.player_id
WHERE gt.group_id = 0 and st.score IN (SELECT min(st2.score) FROM score_table st2)

edited Dec 14 '16 at 12:59

answered Dec 14 '16 at 12:26

Ravi Matani

804
1
8
21

vardump returns null :/ – Kari Dec 14 '16 at 12:44

score 0 · Answer 5 · answered Dec 14 '16 at 12:27

Here first you want to combine score_table and group_table.

Then you want to select only group 0 data from combined table. After that sorting you want to select Minimum score value.

So follow the order to get the results.

SELECT MIN (st.score) FROM score_table st 
INNER JOIN  group_table gt 
ON st.player_id=gt player_id 
WHERE gt.group_id=0;

Select minimum of the column from another table

5 Answers5