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 8 7792
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
"Michelle" <ab*@hotmail.com> wrote in message
news:3f***********************@news.optusnet.com.a u... 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
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.
-------------------------------------------------
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.
"Michelle" <ab*@hotmail.com> wrote in message
news:3f***********************@news.optusnet.com.a u...
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 atsCurVal = 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.
--
Michelle
"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:#C**************@TK2MSFTNGP12.phx.gbl... 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
"Michelle" <ab*@hotmail.com> wrote in message news:3f***********************@news.optusnet.com.a u... 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
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.
"Michelle" <ab*@hotmail.com> wrote in message
news:3f***********************@news.optusnet.com.a u...
Please see reply to Ray
--
Michelle
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:OW**************@TK2MSFTNGP12.phx.gbl... 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.
"Michelle" <ab*@hotmail.com> wrote in message news:3f***********************@news.optusnet.com.a u... 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 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
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:uv**************@tk2msftngp13.phx.gbl... 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.
"Michelle" <ab*@hotmail.com> wrote in message news:3f***********************@news.optusnet.com.a u... Please see reply to Ray
-- Michelle "Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message news:OW**************@TK2MSFTNGP12.phx.gbl... 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.
"Michelle" <ab*@hotmail.com> wrote in message news:3f***********************@news.optusnet.com.a u... 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.
--
Michelle
"Michelle" <ab*@hotmail.com> wrote in message
news:3f***********************@news.optusnet.com.a u... 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
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.
"Michelle" <ab*@hotmail.com> wrote in message
news:3f***********************@news.optusnet.com.a u...
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
"Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message
news:uv**************@tk2msftngp13.phx.gbl... 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.
"Michelle" <ab*@hotmail.com> wrote in message news:3f***********************@news.optusnet.com.a u... Please see reply to Ray
-- Michelle "Chris Barber" <ch***@blue-canoe.co.uk.NOSPAM> wrote in message news:OW**************@TK2MSFTNGP12.phx.gbl... 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.
"Michelle" <ab*@hotmail.com> wrote in message news:3f***********************@news.optusnet.com.a u... 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: DraguVaso |
last post by:
Hi,
I want my application do different actions depending on the exception it
gets.
For exemple: I have an SQL-table with a unique index. In case I try to
Insert a record that's alreaddy in it I...
|
by: Marlene |
last post by:
Hi All
I have the following scenario, where I have found all the duplicates
in a table, based on an order number and a part number (item).I might
have something like this:
Order PODate Rec...
|
by: Themba via AccessMonster.com |
last post by:
I have a table with two columns.it's a unique number and the vendor name.
Number vendor
001 spar
002 spar
003 heckers
004 donald
005 donald
what i want to...
|
by: Killer42 |
last post by:
Hi all.
Sorry, this is probably a really simple one but I'm having some difficulty with it, and don't have much time to devote to it right now.
I need to find all the records which have...
|
by: ak |
last post by:
Is it possible to find repeated(duplicate) element in an array in
single loop ?
AK
|
by: Radu |
last post by:
Hi.
I have a "union" table which results of a union of two tables.
Occasionally I could have duplicates, when the same PIN has been added
to both tables, albeit at different Datees/Times, such...
|
by: ARC |
last post by:
Hello all,
So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".
1st off. On a "Find Duplicates Query", does anyone have a good solution for...
|
by: ryan.paquette |
last post by:
In the table there are 2 fields in which I wish to limit (i.e. No
Duplicates)
Although I do not want to limit them to "No Duplicates" separately.
I need them to be limited to "No Duplicates" as...
|
by: chemlight |
last post by:
I'm trying to figure out how to order an array based on the number of duplicates, and then remove all duplicates.
This is my code right now:
foreach($acID as $searchterms){
$results =...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |