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,the
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
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/
