Hi,

I have this table named Sales with this data


Table: Sales
------------------
Salesman Amount
--------------------
Ben 1
Steve 2
Ben 3

then i do this query

select Salesman, sum(amount) as Total
from Sales
group by Salesman

the results are

---------------------
Salesman Amount
---------------------

Ben 4
Steve 2


The query i want is one that gives me the maximum amount of sales (4).

I have tried this, but it is wrong


select max(Total) from
(select Salesman, sum(amount) as Total
from Sales
group by Salesman)


I am using microsoft SQL Server 2005

thanks in advance!

Try this:

select query1.* from
(SELECT Salesman, Sum(Amount) AS total_amt
FROM Sales
GROUP BY salesman) query1,

(select max(query2.total_amt) as highest_amt
from (SELECT Salesman, Sum(Amount) AS total_amt
FROM Sales
GROUP BY Salesman) query2) query3
where query1.total_amt = query3.highest_amt;

Ummm...why not simply:
?????
Why work harder than that?

That was my first thought. But what happens when the the amount is equal for two different salesmen?

He's using SQL Server, so he could get all of them via http://msdn.microsoft.com/en-us/libr...3(SQL.90).aspx

Yes. Excellent!

Must be the water up there.