Query question

18 Feb 2006 9:26 PM

Dodo Lurker

I have 2 tables. One table (sales) has a salesid and sales amount. The
other table (salesrep) has a salesid and name.

I want to write a query that will return the max sales amount and the name
of the salerep who's responsible for it.

The following returns the max amount for each salesrep. I just want the max
amount for all salesreps and the name of the rep.
i.e.

select s.name, max(s2.amount)
from sales s join salesrep s2 on s.salesid = s2.salesid
group by s.name

Thanks

18 Feb 2006 6:35 PM

Jens

Perhaps you are able to post some ddl and sample data, which would make
the suggestions for us easier. From the point of information I am
having now, I would have done the same query like you did above.

HTH, jens Suessmeyer.

18 Feb 2006 6:53 PM

Roy Harvey

>The following returns the max amount for each salesrep. I just want the max
>amount for all salesreps and the name of the rep.
>i.e.
>
>select s.name, max(s2.amount)
>from sales s join salesrep s2 on s.salesid = s2.salesid
>group by s.name

Perhaps:

SELECT s2.name,s.amount
FROM sales s
JOIN salesrep s2
ON s.salesid = s2.salesid
WHERE s.amount =
(SELECT max(amount) FROM sales)

Roy

18 Feb 2006 6:55 PM

Stu

Is this what you want? Will return the salesperson who had the max
sale across all salespeople.

DECLARE @s TABLE (sid int ,val int)

INSERT INTO @s
SELECT 1,5
UNION ALL
SELECT 1,10
UNION ALL
SELECT 2,3
UNION ALL
SELECT 2,10
UNION ALL
SELECT 2,25

SELECT s.sid, s.val
FROM @s s
WHERE s.val = (SELECT MAX(s.val)
FROM @s s)

18 Feb 2006 7:03 PM

Dave Markle

You may have problems if 2 or more salesreps have the same amount. So
figure out what you want to do there. You can use TOP to limit the
query to a random-ish one if there are ties... You can do something
like this, though:

select s.name, s.amount
from sales s
where s.amount = (select max(amount) from salesrep)

Dodo Lurker wrote:
Show quote

> I have 2 tables. One table (sales) has a salesid and sales amount. The
> other table (salesrep) has a salesid and name.
>
> I want to write a query that will return the max sales amount and the name
> of the salerep who's responsible for it.
>
> The following returns the max amount for each salesrep. I just want the max
> amount for all salesreps and the name of the rep.
> i.e.
>
> select s.name, max(s2.amount)
> from sales s join salesrep s2 on s.salesid = s2.salesid
> group by s.name
>
> Thanks
>
>

19 Feb 2006 3:35 PM

Erland Sommarskog

Dodo Lurker (none@noemailplease) writes:

> I have 2 tables. One table (sales) has a salesid and sales amount. The
> other table (salesrep) has a salesid and name.
>
> I want to write a query that will return the max sales amount and the name
> of the salerep who's responsible for it.
>
> The following returns the max amount for each salesrep. I just want the
> max amount for all salesreps and the name of the rep. i.e.
>
> select s.name, max(s2.amount)
> from sales s join salesrep s2 on s.salesid = s2.salesid
> group by s.name

select TOP 1 WITH TIES s.name, max(s2.amount)
from sales s join salesrep s2 on s.salesid = s2.salesid
group by s.name
order by 2

Less standard than the other solutions posted, though. Works only
on SQL Server.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx