Select Rank Based Query

[trafficman]

Hello,



Newbie here with a query question that I've been struggling with...

I have a table with one to many entries with the field IntID having multiple FUNCTION_CLASS records in some cases. The FUNCTION_CLASS has a number of conditions that I would like to rank and get the table to the point where there is only one IntID record with the highest ranked FUNCTION_CLASS record so it is a 1-to-1 relationship. I ran a duplicate query to mark the IntID records that have >1 FUNCTION_CLASS record, however now I have to strip out all the lower ranked records and just keep the highest.

I'm stuck and wondering if anyone knows how to write a query to address this?

Thanks!

MAP_ID	AGENCY_ID		IntID	FUNCTION_CLASS	Duplicate
236_1	236		1
236_2	236		2
236_3	236		3
236_4	236		4	UL
236_5	236		5
236_6	236		6
236_7	236		7
236_8	236		8
236_9	236		9
236_10	236		10
236_11	236		11
236_12	236		12
236_13	236		13
236_14	236		14
236_15	236		15
236_16	236		16	UL
236_17	236		17	UC	Y
236_17	236		17	UL	Y

[Dal Jeanis]

Okay, so first, please post the structure of the tables. (Just the fields that are significant.) It is not clear from the question how the tables relate to each other. One what to many what? In a normalized situation, you'd have (for example) a table that had agencies, another table that had function classes, and a third table that related them, with the duplicate records. In such a case, you could just write a query that for each agency key in the third table, selected the highest-ranked of the funciton classes in the third table for that agency key.

That is what I would start figuring out in order to suggest how to fix your database.

But, in answer to the particular question that you asked, often, I find that it is simpler in practice to create a new, temporary table, that has the information that you want to keep, then delete all the duplicate information, then add back the correct information.

[trafficman]

Okay, so first, please post the structure of the tables. (Just the fields that are significant.) It is not clear from the question how the tables relate to each other. One what to many what? In a normalized situation, you'd have (for example) a table that had agencies, another table that had function classes, and a third table that related them, with the duplicate records. In such a case, you could just write a query that for each agency key in the third table, selected the highest-ranked of the funciton classes in the third table for that agency key.

That is what I would start figuring out in order to suggest how to fix your database.

But, in answer to the particular question that you asked, often, I find that it is simpler in practice to create a new, temporary table, that has the information that you want to keep, then delete all the duplicate information, then add back the correct information.

Thanks for the quick reply!

All those fields are in the same table. There are more, but I hid the columns in Excel to be able to fit it in here. There should only be one record of IntID with a corresponding record/rank in FUNCTION_CLASS. However, you notice at IntID 17 there are 2 records with 2 different FUNCTION_CLASS records. What I need to do is strip these down to just have the highest ranked FUNCTION_CLASS record corresponding with the IntID.

Would it be easier to create a numeric rank first for the FUNCTION_CLASS?

[Dal Jeanis]

It is difficult to give clear answers without a sense of the purpose of any field, but I'll take a stab at it.

I'm assuming you want either one or zero function class on each record. If the data is as sparse in the table as it is in the example data, then I would have a separate table with only the two significant fields
and an autokey. I assumed INTID is the primary key of the big table. If not, be sure there is one, and use that as the foreign key in the small table.

tblBigStuff
     INTID       AutoKey
     Agency_ID   Number
    MAP_ID      Text

tblAgenFunc
   RecID       Autokey
   INtID       FK to tblBigStuff
   FuncClass   Text

Next, you create records in tblAgenFunc for only the highest (desired) record for each INTID. Then you drop the field from tblBigStuff.

Now, if it's valid for an IntID to have multiple different functions, you can add multiple records, and in the join you select only the highest one. Plenty of ways to handle this one.

[trafficman]

It is difficult to give clear answers without a sense of the purpose of any field, but I'll take a stab at it.

I'm assuming you want either one or zero function class on each record. If the data is as sparse in the table as it is in the example data, then I would have a separate table with only the two significant fields
and an autokey. I assumed INTID is the primary key of the big table. If not, be sure there is one, and use that as the foreign key in the small table.

tblBigStuff
     INTID       AutoKey
     Agency_ID   Number
    MAP_ID      Text

tblAgenFunc
   RecID       Autokey
   INtID       FK to tblBigStuff
   FuncClass   Text

Next, you create records in tblAgenFunc for only the highest (desired) record for each INTID. Then you drop the field from tblBigStuff.

Now, if it's valid for an IntID to have multiple different functions, you can add multiple records, and in the join you select only the highest one. Plenty of ways to handle this one.

Thanks for the help!

Everything is in 1 table... Is it better to split them off? There's probably 15-20 fields and 11500 records total with corresponding information linked up to the IntID