Getting data based on min value of two fields 1

[jpstroud]

I've got a tricky problem to deal with.

First, some background: I work with a directory database. I didn't create the database, and it's VERY frowned upon when I make suggestions to change pieces of it. Needless to say, the database structure in fixed and I cannot change it. Nor am I allowed to go through an interface to edit the data, other than the one they have set up. I've linked the SQL tables to an access database that allows me to manipulate but not change the data itself, meaning I have one route to change the data, and another to view and minipulate it, but they won't let me combine the two for some still unspecified reason.

Now, quite often I'm asked for a list of specific people, usually agency heads, from this database.

Agency heads are found using a ranking system which is the lowest number of in each of two fields being the current head (or fillin). Making sure the agency head is always the same number is those two fields is too time intensive due to the sheer amount of data and my current workload.

The basic structure of what I'm looking for is:
AgencyID - grouped on, TeamID - Min, Ranking - min, EmployeeID

Any thoughts on how to acheive this? Total queries throw the wrong data out since they do each field separately instead of together.

TYIA

 

[GingerR]

can you please list some data examples (at least two outcomes) and what the desired result(s) would be? it would help me understand.
Thanks.

 

[jpstroud]

The final outcome would be based on the information in the query I listed. Just simples numbers would work. As it's a public directory however, I'll give you a couple of real records to peek at.

(I'm not sure how to get it to line up correctly in this forum, if someone lets me know, I'll go back and edit this post.)

============================================================
LDPE_PersonID LDPE_LibraryID LDPE_Position LDPE_Ranking LDPE_TeamID LDPE_LastName LDPE_FirstName
6129 5 Dir 0 0 Everhart Doug
6130 5 Librn 1 0 Van Hess Gina
6131 5 Librn 1 0 Kozloff Rae
6132 5 ILL 2 0 Noyes Esther
6133 5 Lib Assoc, Lead 3 0 Hays Angela
6134 5 Lib Assoc, Lead 3 0 Jones Karene
4858 6 Youth Svcs Librn 1 0 Neuman Mary
5085 6 Dir 0 0 Ashby Jennifer
5944 13 Asst Dir 1 0 Helgoe Gayle
============================================================

Now, by the example text above, the query I want would pull out the lowest Ranking and lowest TeamID per LibraryID. The output from the above sample would show as thus:

============================================================
LDPE_PersonID LDPE_LibraryID LDPE_Position LDPE_Ranking LDPE_TeamID LDPE_LastName LDPE_FirstName
6129 5 Dir 0 0 Everhart Doug
5085 6 Dir 0 0 Ashby Jennifer
5944 13 Asst Dir 1 0 Helgoe Gayle
============================================================

Notice that in LibraryID #13 (third record from results), the record returned is the Asst Dir because there is no Director for that library in the original data. I need to be able to work this into the query. The only record I want per LibraryID is the one with the lowest Ranking and TeamID fields both (ie, Ranking/TeamID - 0/1 before 1/0).

I hope this helps explain what I'm looking for.

 

[PHV]

Something like this ?
SELECT L.* FROM tblLDPE L INNER JOIN (
SELECT LDPE_LibraryID, Min(100*LDPE_Ranking+LDPE_TeamID) As MinRankTeam
FROM tblLDPE GROUP BY LDPE_LibraryID
) M ON L.LDPE_LibraryID=M.LDPE_LibraryID AND 100*L.LDPE_Ranking+L.LDPE_TeamID=M.MinRankTeam
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jpstroud]

The only problem that I can see with that, is that it would evaluate someone with Rank 0 and TeamID 1 as equal to someone with Rank 1 and TeamID 0.

Thank you for your help, I think we're on the same track, but it's a headbanger.

I'm only somewhat used to SQL, so I may be missing an obvious answer on that one.

(confused me for a moment when I cut and pasted, I forgot to modify the table name, too long of a day).

 

[PHV]

I disagree:
Rank 0 and TeamID 1: MinRankTeam=1
Rank 1 and TeamID 0: MinRankTeam=100

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jpstroud]

I see now, I misread the code. Thank you!