Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Finding Closest Match (integer)

Posted on 2013-11-02
Medium Priority
32 Views
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?
0
Question by:azyet24
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 83

Expert Comment

ID: 39619301
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

LVL 46

Expert Comment

ID: 39619355
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 Comment

ID: 39619364
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

Accepted Solution

azyet24 earned 0 total points
ID: 39619388
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month11 days, 10 hours left to enroll