Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    7

    Unanswered: Any alternative way to retreive data

    Hi: Guys
    Given the table below I want a select query that returns the AccountRepID with the largest single sale for each RegionID. In the event of a tie choose any single top AccountRepID to return.

    CREATE TABLE [Sales] (
    [SalesID] [int] IDENTITY (1, 1) NOT NULL ,
    RegionID] [int],
    [AccountRepID] [int],
    [SalesAmount] [money]
    )

    If the data were
    salesid,regionid,accountrepid,salesamount
    1,101,31,$50
    2,101,32,$25
    3,102,31,$25
    4,102,32,$25
    5,102,31,$15

    The query should return
    regionid,accountrepid
    101,31
    102,31 or 102,32


    Is there another way to get the data other than the following query:

    select regionID,accountrepid FROM Sales
    where salesamount in
    (select max(salesamount) FROM Sales group by regionid)

    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    I don't think that will actually get you what you need. What if one region has the exact same salesamount as another region, but it's not the max for that region. You've then returned duplicate rows for that region.

    Try this:

    SELECT sa1.regionID, MAX(sa1.accountrepid)
    FROM
    Sales sa1
    INNER JOIN (
    SELECT regionID, MAX(salesamount) AS salesamount
    FROM Sales) sa2 ON sa1.regionID = sa2.regionID
    AND sa1.salesamount = sa2.salesamount
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    select a.RegionId,a.AccountRepId,a.SalesAmount From Sales a
    Inner join
    (select RegionId,Max(salesAmount) as SalesAmount from Sales group by RegionId) b
    on a.RegionId = b.RegionId and a.SalesAmount = b.SalesAmount

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •