-
Find each salesperson's highest sale
This is not exactly my problem but this is a simple example that will help me. Forgive me but I'm pretty new to SQL.
Let's say I have the following table called SALES:
SALESPERSON DATE AMOUNT
John Doe 1/1/2009 $300
John Doe 3/4/2009 $200
Bill Jones 4/6/2009 $500
Bill Jones 6/8/2009 $800
Sally Smith 5/5/2009 $1000
Each record is a single sale. I want to query this data to return each salesperson's highest sale. The result should be:
SALESPERSON DATE AMOUNT
John Doe 1/1/2009 $300
Bill Jones 6/8/2009 $800
Sally Smith 5/5/2009 $1000
I know that I can return the highest sale in the entire table by using Where AMOUNT = (SELECT MAX(AMOUNT) FROM SALES), but I don't want the single highest sale in the table. I want the highest sale of each salesperson in the table. And I need some of the attributes of that highest sale (like DATE above). I've tried various way of using Group By but it returns nothing. I think this is pretty simple for anyone who knows SQL well. Any help appreciated. Thanks.
Neil
-
ncumbie, You are close to the solution but are getting tripped up by the extra data needed.
As you may know the Group by will work but only to the level of the uniqueness of the columns in the Select list. So finding the SalesPerson or Date in separate queries is easy enough. But together, they are too unique to get just one row for either one.
However, you can use the result of using one of these columns as a 'driver' table that will identify the rows that you want.
Code:
SELECT SALESPERSON, DATE, AMOUNT
FROM table-name A
WHERE AMOUNT = (SELECT MAX(AMOUNT)
FROM table-name B
WHERE A.SALESPERSON = B.SALESPERSON
)
The Sub-Query in this SQL finds the Max Amount for the SalesPerson on the row of the outer SQL that is being examined. Not only does the SalesPerson have to match (a.salesperson = b.salesperson) but the Amount associated for that SalesPerson has to match the Max Amount.
SALESPERSON DATE AMOUNT
John Doe 1/1/2009 $300
John Doe 3/4/2009 $200
Bill Jones 4/6/2009 $500
Bill Jones 6/8/2009 $800
Sally Smith 5/5/2009 $1000
-
Now, what happens if you have :
SALESPERSON DATE AMOUNT
John Doe 1/1/2009 $300
John Doe 3/4/2009 $200
Bill Jones 4/6/2009 $500
Bill Jones 6/8/2009 $800
Sally Smith 5/5/2009 $1000
Sally Smith 5/5/2010 $1000
- The Query will most likely return 2 records for Sally -
What if I want to return only the most recent record for the top sale ?
-
Didmic, one way is:
Code:
SELECT SALESPERSON, DATE, AMOUNT
FROM (SELECT SALESPERSON, DATE, AMOUNT
FROM table-name A
WHERE AMOUNT = (SELECT MAX(AMOUNT)
FROM table-name B
WHERE A.SALESPERSON = B.SALESPERSON
)
) AS A
WHERE DATE = (SELECT MAX(DATE)
FROM table-name B
WHERE A.SALESPERSON = B.SALESPERSON
)
-
The sql command below would give you the desired result:
SELECT FNAME, SALEDATE, MAX(AMOUNT) FROM SALESPERSON GROUP BY fname ;
Hope it suffices.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|