Select Top Ranking Employee for each Company

[mmitchell]

Here is my table of companies and employees with thier title ranks, I need to select just the highest ranking empl for each company.

Comp
anyID	Rank	EmpID
=====================
100	1	104
100	2	103
100	3	101
100	4	102
200	1	202
300	2	301
300	4	302
400	2	402
400	3	401

My problem is how do I get the top ranking EmpID for each company? Like this:

Comp
anyID	Rank	EmpID
=====================
100	1	104
200	1	202
300	2	301
400	2	402

The query I used to get the ranking is this:

SELECT tblMailList.CompanyID, Max(IIf([TitleID]=460,4,IIf([TitleID]=360,3,IIf([TitleID]=390,2,1)))) AS Rank, tblMailList.EmpID
FROM tblMailList
WHERE (((tblMailList.TitleID)=460 Or (tblMailList.TitleID)=360 Or (tblMailList.TitleID)=390 Or (tblMailList.TitleID)=520))
GROUP BY tblMailList.CompanyID, tblMailList.EmpID
ORDER BY tblMailList.CompanyID, Max(IIf([TitleID]=460,4,IIf([TitleID]=360,3,IIf([TitleID]=390,2,1))));

Everything I tried returns all the records because of the grouping, each record is unique., and if I take out what makes it unique then I loose my empID. What am I doing wrong, this should not be so difficult for me, but it is.

 

[mmitchell]

Works !!

I got it to work!

I had to make 4 queies:

1. Ranks
2. Max of Ranks
3. Top Ranking Employee at each Company
4. Top Ranking Employee at each Company for ALL companies

I could not find a way to do it in less, but there probably is.

I have attached the database that worked for "posterity" sake.

=========================
Thanks for the time and trouble,
Mitch

 

[Jon K]

You can do it with 3 queries.

See attached database, which is saved from Access 2000.

.

 

[mmitchell]

Many Thanks!