12-02-2009 05:39 AM
Hi There. I wonder if someone can help.
I have a 2D array of strings (resulting from a database query) One col is a list of job numbers and the other is a list of dates. My SQL query produces results for distinct job numbers and distinct dates. So if there are entries in the database where a job number is split between two or more dates, there will be two (or more) entries.
SELECT DISTINCT SONumber, DateEstimatedShip FROM JobDetails WHERE jobdetails.status <700
What I am trying to do is search through the list of job numbers and list out in a separate array the numbers that are duplicated. I've tried to do this by passing the raw 2D array into a indexing tunnel in a for loop. Then check if there are any duplicates from the next position down in the array to the end. This works fine, but if there are any items that are duplicated more than once I get two entries in my list of duplicates - as in my example below job number 3466 is listed twice as it has three entries in the raw sql table.
How can I turn this array into a list of just the numbers that are duplicated (with no duplicates within it)
I hope I've explained this to a satisfactory level.
Any thoughts will be greatly appreciated.
12-02-2009 06:56 AM
One approach would be to search the "list of duplicates" for new duplicates and delete them (remember to search until the index of the Search 1D Array function returns -1) or you do not insert the item into the list if it is already present (again, search the list but this time within your loop).
hope this helps,
Norbert
12-02-2009 06:58 AM - edited 12-02-2009 06:58 AM
You can perform a second search on your 'list of duplicates' array using the same input as your first search, then if the second search index is <0 you can insert into the 'list of duplicates' array.
(Norbert beat me by a few seconds...)
12-02-2009 08:01 AM - edited 12-02-2009 08:02 AM
You should be able to modify your query to remove the duplicates, e.g.:
SELECT DISTINCT SONumber, DateEstimatedShip FROM JobDetails WHERE jobdetails.status <700 group by SONumber, DateEstimatedShip
/Y
12-03-2009 08:19 AM
Hi fellas,
If you have not already seen it, here is a potencially useful forum link.
Reversing a 2D array by Row and removing duplicates
Best wishes,