tracking online visitors

Question

I want to list the IP addresses and last surfing url of my visitors. The following code I coded works all fine expect one thing. It does not list the last url address but all of them. Example:

71.187.189.67|7/6/2009 9:59:25 PM|/html/default.aspProcess=HomeNewSeason&IMAGECONTENT=bg_home_newtaste.gifMore... 71.187.189.67|7/6/2009 9:59:24 PM|/html/default.aspMore... 66.249.68.210|7/6/2009 9:51:32 PM|/html/default.aspSection=products&SubSection=products&CATEGORYID=2&SORTBY=PriceDownMore...

But I want it as

71.187.189.67|7/6/2009 9:59:25 PM|/html/default.aspProcess=HomeNewSeason&IMAGECONTENT=bg_home_newtaste.gifMore... 66.249.68.210|7/6/2009 9:51:32 PM|/html/default.aspSection=products&SubSection=products&CATEGORYID=2&SORTBY=PriceDownMore...

my code

<%
    OnlineTime = DateAdd("n", -10, Now())

    SQL = "SELECT COUNT(DISTINCT VISITORIP) AS ONLINE, VISITORIP, HTTPADDRESS, DATEENTERED"
    SQL = SQL & " FROM STATS"
    SQL = SQL & " WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "'"
    SQL = SQL & " GROUP BY VISITORIP, HTTPADDRESS, DATEENTERED"
    SQL = SQL & " ORDER BY DATEENTERED DESC"
    Set objOnVisitors = objConn.Execute(SQL)  

    If objOnVisitors.EOF Then
    Else
%>  
      <!-- Start Post -->
      <div class="post">
        <div class="date">
          <span class="month">Online</span>
          <span class="day"><%=objOnVisitors("ONLINE")%></span>
        </div>
        <p>
          <span class="title">Visits Online</span>
        </p>
<% Do While Not objOnVisitors.EOF %>
        <p>
      <%=objOnVisitors("VISITORIP")%>|<%=objOnVisitors("DATEENTERED")%>|<%=objOnVisitors("HTTPADDRESS")%><a href="">More...</a>
        </p>
<%
    objOnVisitors.MoveNext
    Loop
%>

        </div>
        <!-- End Post -->

    End If
    objOnVisitors.Close
    Set objOnVisitors = Nothing
%>

Answer 1

This query should do it. The syntax might be a bit wrong. I'm creating an inner query to find the MAX(DATEENTERED), and joining this onto STATS to restrict the result to the most recent for each IP.

SQL = "SELECT S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED"
SQL = SQL & " FROM STATS S"
SQL = SQL & " JOIN (SELECT VISITORIP, MAX(DATEENTERED) FROM STATS S WHERE DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "') S1"
SQL = SQL & " ON S.VISITORIP = S1.VISITORIP AND S.DATEENTERED = S1.DATEENTERED"
SQL = SQL & " WHERE S.DATEENTERED BETWEEN '" & OnlineTime & "' AND '" & NOW() & "'"
SQL = SQL & " GROUP BY S.VISITORIP, S.HTTPADDRESS, S.DATEENTERED"
SQL = SQL & " ORDER BY S.DATEENTERED DESC"