Need select query

Question

Consider the following table structure with data -

AdjusterID  |  CompanyID  |  FirstName  | LastName | EmailID
============================================================
1001        | Sterling    | Jane        | Stewart  | [email protected]
1002        | Sterling    | David       | Boon     | [email protected]
1003        | PHH         | Irfan       | Ahmed    | [email protected]
1004        | PHH         | Rahul       | Khanna   | [email protected]
============================================================

Where AdjusterID is the primary key. There are no. of adjusters for a company.

I need to have a query that will list single adjuster per company. i.e. I need to get the result as -

========================================================
1001 | Sterling  | Jane  | Stewart | [email protected]
1003 | PHH       | Irfan | Ahmed   | [email protected]
========================================================

If any one could help me that will be great.

Answer 1

One way:

SELECT * FROM Adjusters WHERE AdjusterID IN( SELECT min(AdjusterID) FROM Adjusters GROUP BY CompanyID )

There are a handful of other ways involving unions and iteration, but this one is simple enough to get you started.

Edit: this assumes you want the adjuster with the lowest ID, as per your example :)

Answer 2

I know the answer from Jeremy is a valid one, so I will not repeat it. But you may try another one using a so called tie-breaker:

--//using a tie-breaker. Should be very fast on the PK field
--// but it would be good to have an index on CompanyID
SELECT  t.* 
FROM    MyTable t
WHERE   t.AdjusterID = (SELECT TOP 1 x.AdjusterID FROM MyTable x WHERE x.CompanyID = t.CompanyID ORDER BY AdjusterID)

It could be better performance-wise. But even more useful it is if you had another column in the table and you wanted to select not just one for each company but the best for each company using some other column ranking as a criteria. So instead of ORDER BY AdjusterID, you would order by that other column(s).