Order By inside a left outer join giving random results-CodePudding

I cannot get a sort on a Left Outer Join table to work. The result should be 3900000 however I can only get sorting to work on the players table and not the joined table price

SELECT price FROM price where id = 301;
 --------- 
|  price  |
 --------- 
|  100000 |
| 3900000 |
 ---------

As you can see below, currently my query is returning the lowest of the prices. I would like the result to be 3900000 in this example.

SELECT t.price FROM players s LEFT OUTER JOIN price t on t.id = s.id where t.id = 301 group by s.id order by s.rating desc, t.bod desc;

 --------- 
|  price  |
 --------- 
|  100000 |

Is this possible in MySQL?

CodePudding user response：

I think you want MAX():

SELECT MAX(t.price)
FROM players s
LEFT OUTER JOIN price t on t.id = s.id
WHERE t.id = 301

Or more simply:

SELECT MAX(price)
FROM price
WHERE id = 301

I suspect the column name in price should be player_id instead of id