Tim:
Assuming that in your ealier posting the 99 was supposed to be 999, then
the solution given by Mike Hillyer is excellent and should work.
However, when I read your new posting, I seem to get confused. The
scenario sounds totally different - excuse me - from the earlier one and
would therefore need a different solution. You might help us by giving
sample data.
Or is this what you mean by But I don't want duplicate session numbers
(one is enough)? == In a single session (sessionID) user 999 (userID
999) may visit 3 pages. This results in three inserts being made into
table sti_tracking all having same sessionID and userID. Correct? When
retrieving you do not want to retrieve all these three records. Correct?
You just want one of the records. Which one? The first, second or third
because they each probably have a different time and pageName (even
date!!). If you did not want the date, time and pageName then the
solution is simple SELECT DISTINCT userID, sessionID FROM sti_tracking
WHERE userID = 999.
If you do not care which of the entries (3 in my example) is returned
and you still want the date, time and pageName (my guess is the first
will be returned), then you need to generate all the distinct userID and
sessionID pairs using the above SQL. Then for each pair (use a loop) run
SELECT userID, sessionID, date, time, pageName FROM sti_tracking WHERE
userID = {provide from loop} AND sessionID = {provide from loop} LIMIT 1.
Peter Aganyo
Tim Winters wrote:
Hello,
Very sorry to everyone about the confusing message. I should have read
it over again before pressing send.
First of all I'm looking for userID 999. A typo in the message not in
the code.
The table is set up like this.
Table name sti_tracking
hitID (primary key) (autonumber)
userID
sessionID
date
time
pageName
What it's for is a simple page tracing counter for a FLash site. Each
time a section is accessed a new row is written in the table.
userID identifies the user. So if the user comes to the site today and
comes back again tomorrow the userID will be maintained.
sessionID identifies 1 visit to the site. During 1 visit a user may
view many sections within the site but as long as he doesn't close the
browser the session number remains the same. Date and time will always
be different (as will the hitID obviously).
So what I want to be able to do is single out a user (999) and retrieve
all the sessions he was involved in. But I don't want duplicate session
numbers (one is enough).
Make any more sense?
Tim Winters
Creative Development Manager
Sampling Technologies Incorporated
1600 Bedford Highway, Suite 212
Bedford, Nova Scotia
B4A 1E8
www.samplingtechnologies.com
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Office: 902 450 5500
Cell: 902 430 8498
Fax:: 902 484 7115
-Original Message-
From: William R. Mussatto [mailto:[EMAIL PROTECTED]
Sent: July 2, 2003 2:13 PM
To: [EMAIL PROTECTED]
Subject: Re: Newbie SELECT problem
Hello everyone,
I have the following select statement
SELECT DISTINCT sessionID, userID, date, time
FROM sti_tracking
WHERE userID = 999
What I want is to have only records with the userID of 99 and where
the
sessionID is distinct (meaning only on of each session id). Neither
sessionID nor userID are keys or unique.
Obviously this isn't working.
Can someone suggest how this should be done?
Tim Winters
Creative Development Manager
Sampling Technologies Incorporated
Had a similar experience, and I've been doing it long enough to know
better. 'DISTINCT' would work only if date and time returned the
same
values.
Are '999' and '99' supposed to be the same?
Let me see if I can rephrase what you are looking for:
a. For user '999' give me the information where there is only one
record
with a given SessionID?
b. For user '999' for each sessionID give me the unique Date and Time
values.
c. something else entirely.
Also, are you running this in a procedureal language (e.g., perl, java)?
This will give us other options.
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
Your favorite stores, helpful shopping tools and great gift ideas.
Experience the convenience of buying online with [EMAIL PROTECTED]
http://shopnow.netscape.com/
