Part of the problem is that you have the date and time in different fields,
making it difficult to select the MAX() (maximum) time/date.
Try (for SQL Server):
SELECT
MAX(CAST([Date] + [Time]) AS SmallDateTime),
SessionID,
FROM
TableNameHere
GROUP BY
SessionID,
-or, if you know the date -
SELECT
MAX(Time),
SessionID,
FROM
TableNameHere
WHERE
Date = '2002/18/9'
GROUP BY
SessionID
I assume that you don't really have fields called "Date" and "Time" - those
are bad names.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "M. H. K." <[EMAIL PROTECTED]>
Subject: Something to do with GROUP BY and HAVING
: I record all the web site activities in a SQL table. I embedded an iframe
: on all the pages accross the site with which I submit the iframe's
: container's URL into a SQL table along with ip/time/sessionID. So when a
: user goes from one page to another page within my site, they are tracked.
:
: The table has the following fields;
:
: autoID | SessionID | userIP | locationURL | date | Time
:
: With this table in place, I have the ability to query which IP is on which
: page and what the time was at the time of access.
:
: This is good enough to implement "How many visitors on the site right now
: ?" type of functionality. Or even better , you can even tell how many
: visitors and which URL they are.
:
: All one needs then is to build a SQL query giving all the session ID's in
: that table along with each user's *latest locationURL* within the last X
: minutes. That X could be passed thru a query string but for the time being
: say that for one minute... ( 1 minute is practically the same as asking
: the almost the "right now" )
:
: Following query you'll see is an attempt towards this goal; But I need
: some help in to do exactly what I have planned to do.
:
: I hard coded DATE and TIME values for easier reading. ( In the final
: version, of course, they will be parameter driven. )
:
: Select sessionID,userIP,locationURL,dateTime from THAT_TABLE WHERE Date =
: '08/15/2002' AND Time > '13:35:00' Order by Time DESC
:
: With the above SQL, you would collect all the records that have been
: placed after Date = '08/15/2002' AND Time > '13:35:00 PM'.
:
: But this is not good enough. Actually, it is more than enough.
:
: I would potentially get multiple records for the same user ( sessionID )
: if that user happened to browse a few pages after '13:35:00 PM'
:
: My challenge is in eliminating the older records for each user.
:
: I want to get one record per user and that record should be the latest
: entry for him.
:
: I know I have to deal with the "group by and "having" keywords available
: in SQL to accomplish this goal.
:
: So any help towards this is appreciated. Thank you
:
---
You are currently subscribed to activeserverpages as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
