Results 1 to 5 of 5

Thread: Find each salesperson's highest sale

Hybrid View

  1. #1
    Join Date
    Mar 2010
    Posts
    1

    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

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    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

  3. #3
    Join Date
    Aug 2010
    Posts
    1
    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 ?

  4. #4
    Join Date
    Apr 2009
    Posts
    86
    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 
                 )

  5. #5
    Join Date
    Aug 2010
    Posts
    2

    Thumbs up

    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
  •