identify duplicates in an array and number of times duplicated

[Michelle]

hi,

i have created an array from recordset containing user names
eg. (davidp, davidp, evenf, patricka, rebeccah)

which i have sorted in alphabetical order, but i need to
identify duplicates in this array and the number of times it has
been duplicated.

can someone help?

 

[Ray at]

If you've already sorted your array alphabetically, you can do:

Dim sCurVal, sLastVal, iDups
iDups = 0
For i = LBound(YourArray) To UBound(YourArray)
sCurVal = YourArray(i)
If sCurVal = sLastVal Then iDups = iDups + 1
sLastVal = sCurVal
Next


This will just give you the total number of duplicate items, like, if your
array is

a,b,c,c,d,e,e,e,f,g

iDups will return as 3, since there is one extra c and two extra e's.

Is that what you wanted, or did you want to be able to say:
c has 1 duplicate
e has two duplicates
etc.

Ray at work

 

[dlbjr]

Why not do a count in the SQL query string and get the username and count from the recordset?



-------------------------------------------------
d l b j r

Unambit from meager knowledge of inane others,
engender uncharted sagacity.
-------------------------------------------------

 

[Chris Barber]

You'd be better off getting another recordset with the duplicate counts
already created for you?

SELECT [Name] as UserName, Count([Name]) as CountOfUserName
FROM YourTable
GROUP BY [Name]
ORDER BY Count([Name]) DESC

This will give you:

UserName CountOfUserName
Chris 10
Dave 7
Henrik 2
Michale 1
Joan 1

etc.

If you only want the duplicates listed then change it to be:

SELECT [Name] as UserName, Count([Name]) as CountOfUserName
FROM YourTable
WHERE Count([Name]) > 1
GROUP BY [Name]
ORDER BY Count([Name]) DESC

Hope this helps.

Chris.

hi,

i have created an array from recordset containing user names
eg. (davidp, davidp, evenf, patricka, rebeccah)

which i have sorted in alphabetical order, but i need to
identify duplicates in this array and the number of times it has
been duplicated.

can someone help?

 

[Michelle]

Hi Ray

Is that what you wanted, or did you want to be able to say:
c has 1 duplicate
e has two duplicates

Yes this is what i want

I tried to use your code, although i had to add 'end if', it gave me a
a 'Subscript out of range' error at

sCurVal = YourArray(i)

----------------------------------------------
this is my code for the array
----------------------------------------------
<%
Dim MyArray, sOutput
MyArray = rs.GetRows()
MyArray = arraysort(MyArray)

Dim iRowLoop
For iRowLoop = 0 to UBound(MyArray, 2)
sOutput = trim(left(MyArray(iColLoop, iRowLoop),10)) & "<br> "
Response.Write(sOutput)
Next
%>
----------------------------------------------

Actually the rows in the array is unique, the source is a text file dump of
terminal service processes, but i need to extract the user names of those
using a particular process twice, and how many times they have these
processes open. I cannot sort or search for duplicates in SQL, because the
first line contains a lot of spaces inbetween the headings which dosnt make
a qualified column name.

Hope you can help.

 

[Chris Barber]

Re:
I cannot sort or search for duplicates in SQL, because the
first line contains a lot of spaces in-between the headings which doesn't
make
a qualified column name.

That doesn't seem to make sense - in order to get a recordset you must be
specifying some SQL? You can determine the 'real' field names by enumerating
the fields collection or just use the numeric field indexes 0 to however
many fields.

eg. SELECT TOP 1 * FROM Table

will get a single row recordset that you can look at to get the field names.

However, you know what you have so I'll stop there and let you decide what's
easiest to implement (array manipulation or SQL).

Chris.

Please see reply to Ray

 

[Michelle]

Hi Chris

This is the SQL i am using

sql="SELECT * from process.txt"

process.txt is a text file dumped from ms-dos and this is the first 6 lines
from this file
--------------------------------------------------------
USERNAME SESSIONNAME ID PID IMAGE
davidp id 1 1 3472 notepad.exe
davidp id 9 9 4348 notepad.exe
evenf id 8 8 5160 notepad.exe
patricka id 19 19 7904 notepad.exe
rebeccah id 10 10 2820 notepad.exe
--------------------------------------------------------
since this file isnt delimited, all this data is retrieved into one column
and the first line contains spaces, so i cannot do

sql="SELECT 'USERNAME SESSIONNAME ID PID IMAGE'
from process.txt"
or any other sql statement that requires i directly name the column

You can determine the 'real' field names by enumerating
the fields collection or just use the numeric field indexes 0 to however

how can i do this in ASP?

I want to be able to show that davidp is using a program twice
eg. davidp (2)

--
Michelle

Re:
I cannot sort or search for duplicates in SQL, because the
first line contains a lot of spaces in-between the headings which doesn't
make
a qualified column name.

That doesn't seem to make sense - in order to get a recordset you must be
specifying some SQL? You can determine the 'real' field names by enumerating
the fields collection or just use the numeric field indexes 0 to however
many fields.

eg. SELECT TOP 1 * FROM Table

will get a single row recordset that you can look at to get the field names.

However, you know what you have so I'll stop there and let you decide what's
easiest to implement (array manipulation or SQL).

Chris.

Please see reply to Ray

--
Michelle

You'd be better off getting another recordset with the duplicate counts
already created for you?

SELECT [Name] as UserName, Count([Name]) as CountOfUserName
FROM YourTable
GROUP BY [Name]
ORDER BY Count([Name]) DESC

This will give you:

UserName CountOfUserName
Chris 10
Dave 7
Henrik 2
Michale 1
Joan 1

etc.

If you only want the duplicates listed then change it to be:

SELECT [Name] as UserName, Count([Name]) as CountOfUserName
FROM YourTable
WHERE Count([Name]) > 1
GROUP BY [Name]
ORDER BY Count([Name]) DESC

Hope this helps.

Chris.

hi,

i have created an array from recordset containing user names
eg. (davidp, davidp, evenf, patricka, rebeccah)

which i have sorted in alphabetical order, but i need to
identify duplicates in this array and the number of times it has
been duplicated.

can someone help?

 

[Michelle]

Hi All

Thank you for your inputs, but i have solved my problem,
i didnt ask the right question in the beginning, what i needed was
to search a string for duplicates and the number of times the value
had been duplicated.

But what i had was, an array of unique values, which i manipulated
and displayed to show some duplicates.

Thanks again.

 

[Chris Barber]

Perhaps this is space or fixed width delimited?
It is possible to query a text file that is space or fixed width delimited
by column names but I'm pretty sure you're already aware of that and may
even have tried it.

Cheers,

Chris.

Hi Chris

This is the SQL i am using

sql="SELECT * from process.txt"

process.txt is a text file dumped from ms-dos and this is the first 6 lines
from this file
--------------------------------------------------------
USERNAME SESSIONNAME ID PID IMAGE
davidp id 1 1 3472 notepad.exe
davidp id 9 9 4348 notepad.exe
evenf id 8 8 5160 notepad.exe
patricka id 19 19 7904 notepad.exe
rebeccah id 10 10 2820 notepad.exe
--------------------------------------------------------
since this file isnt delimited, all this data is retrieved into one column
and the first line contains spaces, so i cannot do

sql="SELECT 'USERNAME SESSIONNAME ID PID IMAGE'
from process.txt"
or any other sql statement that requires i directly name the column

You can determine the 'real' field names by enumerating
the fields collection or just use the numeric field indexes 0 to however

how can i do this in ASP?

I want to be able to show that davidp is using a program twice
eg. davidp (2)

--
Michelle

Re:
I cannot sort or search for duplicates in SQL, because the
first line contains a lot of spaces in-between the headings which doesn't
make
a qualified column name.

That doesn't seem to make sense - in order to get a recordset you must be
specifying some SQL? You can determine the 'real' field names by enumerating
the fields collection or just use the numeric field indexes 0 to however
many fields.

eg. SELECT TOP 1 * FROM Table

will get a single row recordset that you can look at to get the field names.

However, you know what you have so I'll stop there and let you decide what's
easiest to implement (array manipulation or SQL).

Chris.

Please see reply to Ray

--
Michelle

You'd be better off getting another recordset with the duplicate counts
already created for you?

SELECT [Name] as UserName, Count([Name]) as CountOfUserName
FROM YourTable
GROUP BY [Name]
ORDER BY Count([Name]) DESC

This will give you:

UserName CountOfUserName
Chris 10
Dave 7
Henrik 2
Michale 1
Joan 1

etc.

If you only want the duplicates listed then change it to be:

SELECT [Name] as UserName, Count([Name]) as CountOfUserName
FROM YourTable
WHERE Count([Name]) > 1
GROUP BY [Name]
ORDER BY Count([Name]) DESC

Hope this helps.

Chris.

hi,

i have created an array from recordset containing user names
eg. (davidp, davidp, evenf, patricka, rebeccah)

which i have sorted in alphabetical order, but i need to
identify duplicates in this array and the number of times it has
been duplicated.

can someone help?