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)

ThanksI 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|||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