Using max statements - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using max statements

Author

Topic

ravenhawk08
Starting Member

8 Posts

Posted - 2009-06-11 : 16:55:56

This may be a basic SQL question but I am having trouble finding an answer.

Lets say you have a table that contains the following columns (Salesperson, location, client, salesdate, sale_amount)

I want to identify each salesperson and their biggest sale base on sales_amount. The output needs to include each of the above fields. Each person could have multiple sales on the same day.

How would I structure the SQL statement? I've tried pulling data using a max statement but I'm getting the maxium value for each day instead of just the highest amount regardless of the day.

HELP!

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-11 : 19:51:51

[code]
select salesperson, max(sales_amount)
from table
group by salesperson
[/code]

KH
[spoiler]Time is always against us[/spoiler]

yonabout
Posting Yak Master

112 Posts

Posted - 2009-06-15 : 06:00:25

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources