# Finding Closest Match (integer)

I am writing a program that will match two entries based on weight within 2 lbs.  I have a table with the following structure:

entry#    weight
1            135
2            137
3            154
4            190
5            175
6            188
7            193
8            177
9            210
10          215
11          235
12          238
13          240

Each entry can only be matched up against one other entry...no duplicates.  Weight must be within 2 lbs (either above or below) and can not exceed 2 lbs.  All entries left over (no match found) will be listed as "no match".  I am writting this in VB.NET and SQLLite b/c the program will need to be portable. This is a Windows form.

From above
entry 1 matches entry 2
entry 3 No Match
entry 4 matches  entry 6
entry 5 matches entry 8
entry 7 no match
etc...

Where I'm stuck is in the weight matching to 1) find a match within 2 lbs (up or down so maybe that's 4lbs ??) 2) preventing the same entry from matching itself (like on a join).

What's my best options for this task?
###### Who is Participating?

Author Commented:
I'm making progress but this has gotten a bit more difficult now b/c I need to now introduce teams into the code.  Meaning, I still need to match up weight but team members can't be matched against each other.

In my code below I am successful with matching all records when ignoring teams, but from here I'm not sure how I'd keep up with multiple entries from same team.

Here's my code so far:

``````Dim sql As String = "select id,team,entry,band,weight from entries order by weight asc"

For Each Dr As DataRow In ds.Tables(0).Rows
Dim team As String = Dr(1)
Dim entry As String = Dr(2)
Dim band As String = Dr(3)
Dim weight As String = Dr(4)

If counter > 0 Then ' first record would not have previous to compare so we skip it

If (weight - previousweight <= 2) Then
'Add previous and current record to dgassignment as a match
dgAssignments.Rows.Add(previousentry, previousband, (Int(previousweight / 16)).ToString & "lbs " & (previousweight Mod 16) & "oz", entry, band, (Int(weight / 16)).ToString & "lbs " & (weight Mod 16) & "oz")
'clear previous since both prev and current are match
previousteam = 0
previousentry = 0
previousband = 0
previousweight = 0
Else
'Add previous to no match datagrid
dgNoMatch.Rows.Add(previousteam, previousentry, previousband, (Int(previousweight / 16)).ToString & "lbs " & (previousweight Mod 16) & "oz")

'Add current entry as previous to be used in next loop
previousteam = team
previousentry = entry
previousband = band
previousweight = weight
End If
Else
previousteam = team
previousentry = entry
previousband = band
previousweight = weight
End If
counter = counter + 1
Next
``````
0

Commented:
I think one way of achieving this would be to sort the list. This way, you can match each enter up and down until the difference is more than 2.
0

Data Warehouse Architect / DBACommented:
One of the issues that you have is that there's not enough separation between the first two weights.  A weight of 136 matches both entry 1 and 2.
``````SELECT entry# FROM {tablename}
WHERE weight >= MyWeight -2
AND weight <= MyWeight + 2;
``````

That should do it, once you resolve the overlap between 1 and 2.  And SQLLITE will handle that just fine...

Good Luck,
Kent
0

Author Commented:
Kent, so if I sort the list ASC and then set the first value as MyWeight I can see the first iteration to match up with the second record.  So I would then set MyWeight as the 3rd record and proceed unti it finds one.  So I guess I'd need to store records that didn't match in an array so that I'd know the ones that were not a match (there could be hundreds of records in list).  Is that what you had in mind by your MyWeight reference?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.