Hi,
If you want all the values from your original table, I'd try something like this:create table #sales (Salesperson varchar(10), location varchar(10), client varchar(10), salesdate datetime, sale_amount decimal (10,2))
insert #sales
select 'John', 'London', 'ABC', '01/01/2009', 100.00 union
select 'John', 'London', 'ABC', '01/02/2009', 200.00 union
select 'John', 'Norwich', 'DEF', '01/03/2009', 312.00 union
select 'sarah', 'Norwich', 'DEF', '01/01/2009', 250.00 union
select 'sarah', 'Norwich', 'DEF', '01/05/2009', 100.00 union
select 'sarah', 'London', 'ABC', '01/01/2009', 90.00 union
select 'sarah', 'London', 'ABC', '01/02/2009', 400.00 union
select 'fernando', 'Liverpool', 'GHI', '01/06/2009', 75.00 union
select 'fernando', 'Liverpool', 'GHI', '01/04/2009', 100.00
select
s.salesperson,
s.location,
s.client,
s.salesdate,
s.sale_amount
from
#sales s
join
(select
salesperson,
max(sale_amount) sale_amount
from #sales
group by salesperson) ms
on s.salesperson = ms.salesperson
and s.sale_amount = ms.sale_amount
drop table #sales
You create an alias table with just the max sale and the sale person in it (ms). You can then join this back to the sales table and the two values together act like a unique key.
Obviously, if you have a number of maximum sales at the same value, it will bring back more rows.
Cheers,
Yonabout 
Using max statements
Posted - 2009-06-11 : 16:55:56
