mathing records again

[Alicia]

Hi,

 

I am trying to something like below and hope some guru can advise how to accomplish this :

 

In my db, I have a column with all random numbers : e.g : 3213, 1234, 1235, 3132, 8231 and etc, with more than 300 records

 

Now I want to do something like match all the numbers like 3213 and permuted numbers of 3213 with all other numbers and count +1 if the other numbers matched other rows in the table.

 

e.g :

I have these rows in the table :

1)3231

2)3211

3)1213

4)1231

5)1234

 

I am thinking to do something like this to get what I want : We take 1)3231 as an example :

First, I will get all permuted numbers for the 1) 3231

*3231

* 3213

* 1323

and etc

 

then assign each of the number into an array

 

from the array, I will do another round of matching with all other records from other rows for each number stored in the array.

e.g : take *3231 to match with other rows in the table

2)3211

3)1213

4)1231

5)1234

 

if not found, then count is 0

 

then move on to the second permuted number *3213 to match with other records

2)3211

3)1213

4)1231

5)1234

 

Same goes to the others.

 

After all the permuted number for 1) 3231 have been matched with other rows, we will move on to the other row number - 2) 3211 and do the same process all over again... get all permuted numbers for 3211, assign to array and do matching with all other rows.

 

I am wondering is there any more simple way to accomplish this function?

 

Hope gurus can get what I am trying to do and share with me your ideas. Thanks

[fenway]

This is a job for application code.

[kickstart]

Hi

 

While I can see a way to play around with fixed permutations, I am not that sure exactly what it is you want.

 

For example with 3231 the possible permutations are:-

 

(3231, 3213, 3321, 3312, 3123, 3132, 2313, 2133, 3213, 3123, 1233, 1323, 2331, 2313, 3321, 3312, 1323, 1332, 2331, 2133, 3231, 3132, 1233, 1332)

 

Do you want to match all those permutations against themselves, or against the ~300 numbers on your original table?

 

Or do you want every permutation of each number and count the number of other permutations of other number? Or the count of other numbers which have the same permutation?

 

All the best

 

Keith

[kickstart]

Hi

 

Right, had a play with what I think you want to do.

 

Set up 2 tables. One with a list of numbers called PermNumbers, containing a unique ID and a 4 digit number as a CHAR(4) in a column called SomeNum. The other called Permutations has 5 columns, first being a unique ID and then a column for each character position (ie, 4 character position, called Pos1, Pos2, Pos3 and Pos4). This 2nd table has one row for each possible combination of 4 characters.

 

From these you can use the following SQL to get every possible permutation for each number in the first table:-

 

SELECT b.SomeNum AS SomeNum1, concat( substr( b.SomeNum, Pos1, 1 ) , substr( b.SomeNum, Pos2, 1 ) , substr( b.SomeNum, Pos3, 1 ) , substr( b.SomeNum, Pos4, 1 ) ) AS Permutation1
FROM Permutations a, PermNumbers b

 

Note that if your number was (say) 3333 then although there are still the same number of permutations the results are all the same. For example if you had 3123 then 1233 and 1233 are both possible combinations.

 

If you JOIN this back on the table of numbers and do a count you will get a list of the numbers and a count of permutations that match it. Check that the base numbers are not the same on the join and you can exclude matches against itself.

 

SELECT y.SomeNum, COUNT( z.SomeNum1 ) 
FROM PermNumbers y
LEFT OUTER JOIN (
SELECT b.SomeNum AS SomeNum1, concat( substr( b.SomeNum, Pos1, 1 ) , substr( b.SomeNum, Pos2, 1 ) , substr( b.SomeNum, Pos3, 1 ) , substr( b.SomeNum, Pos4, 1 ) ) AS Permutation1
FROM Permutations a, PermNumbers b
)z ON y.SomeNum = z.Permutation1
AND y.SomeNum != z.SomeNum1
GROUP BY y.SomeNum

 

Far from sure this is what you want, and it will not work if the numbers are not 4 digit numbers (although it could be modified to, mixing 4 and 5 digit numbers would make it more of a nightmare).

 

All the best

 

Keith