I do hope that I'm posting this in the right place. I need some help with an sql that I just cannot understand why it doesn't produce the right results.
I've got a master-detail table (accounts-history) with account details of users including a country location (country.id) pointing to a countries table. I'm trying to retrieve the records from the master-detail table of the largest value (history.value) for each country in a given month (history.month). I've tried many different sqls but cannot see why the following does not retrieve the correct records so would appreciate some help
SELECT accounts.id, accounts.username, accounts.countryid, history.value
FROM accounts
INNER JOIN histroy ON history.id=accounts.id
WHERE accounts.verified=1 AND history.month=35
GROUP BY accounts.countryid
ORDER BY accounts.countryid, history.value DESC