2

I have these records

enter image description here

What I want is to find max value in balance and retrieve it with the associated name (I have foreign keys userid==balanceID), for example in this case it would be "name2 | 2000". Is here any Mysql'ist who could tell me how to achieve this?

I tried something similar to 

SELECT MAX(b.balance), i.name 
from balance b, usr i 
WHERE i.userid=b.balanceID;

BUT it doesnt assign correct name to MAX value from balance. Any help?;]

John Woo
  • 258,903
  • 69
  • 498
  • 492
Omega wow
  • 35
  • 3

1 Answers1

3

You need to add GROUP BY clause in your query.

SELECT   MAX(b.balance) as Maximum_Balance, i.name 
FROM     balance b INNER JOIN usr i 
            ON i.userid=b.balanceID
GROUP BY i.name;

or maybe you only want to find the person with the highrd balance right?

SELECT  b.balance  as Maximum_Balance, i.name 
FROM     balance b INNER JOIN usr i 
            ON i.userid=b.balanceID
WHERE   b.balance = (SELECT MAX(balance) FROM balance)

SQLFiddle Demo

John Woo
  • 258,903
  • 69
  • 498
  • 492