Column is invalid in the select list because it is

问题:

Clearly, when GROUP BY clause used, columns that are not aggregate function should be part of the group by clause. The problem here is, I cannot contain HTTPADDRESS & DATEENTERED columns in GROUP BY clause. Also, I dont know a function that will give me the latest entries of all.

edit: I use sql-server. I would use LAST function if I were using access.

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

回答1:

You have to self-join back:

WITH LASTVISIT AS (
    SELECT VISITORIP, MAX(DATEENTERED) AS DATEENTERED
    FROM STATS
    WHERE DATEENTERED BETWEEN @STARTTIME AND @ENDTIME
    GROUP BY VISITORIP
)
SELECT STATS.VISITORIP, STATS.HTTPADDRESS, STATS.DATEENTERED
FROM STATS
INNER JOIN LASTVISIT
    ON LASTVISIT.VISITORIP = STATS.VISITORIP
    AND LASTVISIT.DATEENTERED = STATS.DATEENTERED
ORDER BY STATS.DATEENTERED DESC

Note, this assumes a given VISITORIP will have a unique maximum DATEENTERED in the range.