biggest value

________

Hi,
I need help with a query. I got two tables that looks like this:

table1

..id..|..sum..|..who..|..item..|

...0..|..100..|....2.....|......1...|
...1..|..150..|....3.....|......1...|
...2..|..200..|....3.....|......2...|

The item value in table1 reffers to the id of table2.

I need a query that gets the fields where a user (who) got the biggest sum and the name from table 2. I tryed to left join the with this query.
SELECT max(a.sum), b.name FROM table1 AS a LEFT JOIN table2 AS b ON a.item = b.id WHERE a.who = '1' GROUP BY a.item

The problem with that query is that is gets the bigges sum of that user... and I only need the info if the user got the bigges sum on any items!

I have been trying for a day now... but without any luck!
Need help 🙂

programmer79

Is there ever going to be two max sums that are exactly alike. If not you could query table1 and loop through the results. Once you have determined the max sum you could requery table2.

________

All I actuale need is a query that finds out if a user got the max sum of any item.

tomhath

I think this is what you want:

SELECT a.who, b.name, a.total
FROM table1 a
INNER JOIN table2 b ON a.item = b.id
ORDER BY a.total DESC 
LIMIT 1

Notice I changed the column you called "sum" to "total" because "sum" is a SQL function and should not be used as a column name.

________

That did not work...

The problem is that I need a query that returns information if the user got the max sum.

All I need is a query that finds out if ex. user 5 has the max sum of any item... I don't need the join the tables, that I can fix later... I need help with the query to find out if the user got the max sum of any item!