Selecting the most used ipval

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

Hi,

In a table I log the user's IP when the connexion is made. I'd like to query
the mostly used IP, meaning:



userID date IP

1 10012006 1.1.1.10

1 10022006 1.1.1.10

1 10022006 1.1.1.13

1 10032006 1.1.1.15

1 10042006 1.1.1.10

1 10052006 1.1.1.10



Results:

userID 1 uses mostly IP 1.1.1.10

TIA

Ron
 
First query (Query1):
SELECT UserID, IP, Count(UserID) as Frequency
FROM YourConnectionTable
GROUP BY UserID, IP

Second query (Query2):
SELECT UserID, Max(Frequency) as MaxUse
FROM Query1
GROUP BY UserID

Third Query:
SELECT Query1.UserID, Query1.IP
FROM Query1 INNER JOIN Query2
ON Query1.UserId = Query2.UserID
AND Query1.Frequency = Query2.MaxUse

That can all be done in one query if your field and table names don't
contain spaces (or other characters that require you to use [] around the
name). That should look something like the following.

SELECT Query1.UserID, Query1.IP
FROM (
SELECT UserID, IP, Count(UserID) as Frequency
FROM YourConnectionTable
GROUP BY UserID, IP) as Query1
INNER JOIN (
SELECT UserID, Max(Frequency) as MaxUse
FROM (
SELECT UserID, IP, Count(UserID) as Frequency
FROM YourConnectionTable
GROUP BY UserID, IP) as Temp
GROUP BY UserID) as Query2
ON Query1.UserId = Query2.UserID
AND Query1.Frequency = Query2.MaxUse
 
Back
Top