CODEWITHSUNDEEP
sqlsql-server
user2145893
user2145893

Reputation: 135

SQL query match specific IP address

I have a table with data like this: 

IP             ACCESS_DATE
----------------------------
192.168.1.5    06/22/2018
192.168.1.5    06/20/2018
192.168.1.5    06/18/2018
192.168.1.7    06/22/2018
192.168.1.7    05/01/2018
192.168.1.212  06/22/2018
10.10.10.1     06/22/2018

This is SQL Server, so no inet possible...

My IP column is defined as varchar(20).

I have a query that lists the most recent date associated to all IP addresses in that table, and I believe it works just fine. However, I can't seem to have it return the result for a specific IP.

This is the query that works: 

SELECT 
    t.IP, t.ACCESSS_DATE 
FROM
    (SELECT 
         IP, MAX(ACCESSS_DATE) AS ACCESSS_DATE
     FROM  
         USER_ACCESS_HISTORY
     GROUP BY
         IP) x 
JOIN 
    USER_ACCESS_HISTORY t ON x.IP = t.ip
                          AND x.ACCESSS_DATE = t.ACCESSS_DATE

but if I do this: 

SELECT 
    t.IP, t.ACCESSS_DATE 
FROM
    (SELECT 
         IP, MAX(ACCESSS_DATE) AS ACCESSS_DATE
     FROM
         USER_ACCESS_HISTORY
     GROUP BY IP) x 
JOIN 
    USER_ACCESS_HISTORY t ON x.IP = '192.168.1.5'
                          AND x.ACCESSS_DATE = t.ACCESSS_DATE

it returns 

192.168.1.5    06/22/2018
192.168.1.7    06/22/2018
192.168.1.212  06/22/2018

Any help would be appreciated!

Upvotes: 0

Views: 2151

Answers (4)

critical_error
critical_error

Reputation: 6716

Extending upon Cetin Basoz's answer, another alternate way of getting the most recent date would be:

SELECT TOP 1 [ip], [access_date] FROM @ips WHERE [ip] = '192.168.1.5' ORDER BY [access_date] DESC;

I looked into the time statistics and both MAX and TOP were the same. However, with such a small dataset that is expected.

You can run the following against you real data to see which one performs faster.

Using TOP:

SET STATISTICS TIME ON

SELECT TOP 1 [ip], [access_date] FROM @ips WHERE [ip] = '192.168.1.5' ORDER BY [access_date] DESC;

SET STATISTICS TIME OFF

Using MAX:

SET STATISTICS TIME ON

SELECT [ip], MAX( [access_date] ) AS access_date FROM @ips WHERE [ip] = '192.168.1.5' GROUP BY [ip];

SET STATISTICS TIME OFF

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23837

Since you already know the IP, you don't need any joins in fact:

SELECT IP, MAX(ACCESSS_DATE) AS ACCESSS_DATE
from USER_ACCESS_HISTORY
where IP ='192.168.1.5'
group by IP

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

Changing this:

ON x.IP =t.ip

to this:

ON x.IP ='192.168.1.5'

breaks the relationship in the query between the USER_ACCESS_HISTORY table and the subquery, so nothing is correlated anymore. You want this:

SELECT t.IP, t.ACCESSS_DATE 
FROM( 
    SELECT IP,MAX(ACCESSS_DATE) AS ACCESSS_DATE
    FROM USER_ACCESS_HISTORY
     GROUP BY IP
) x 
JOIN USER_ACCESS_HISTORY t ON x.IP =t.ip
    AND x.ACCESSS_DATE = t.ACCESSS_DATE
WHERE x.IP ='192.168.1.5'

This preserves the relationship and also filters the data to just the desired IP.

Even that much seems more complicated than you need (just the subquery would be enough), but I'll assume we're only seeing a condensed version of the query to illustrate the problem. I do bring it up just in case you can reduce the query like this:

SELECT IP, MAX(ACCESSS_DATE) AS ACCESSS_DATE
FROM USER_ACCESS_HISTORY
WHERE IP = '192.168.1.5'
GROUP BY IP

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Your versions get all ips that have the same date as the maximum date for your given ip. That is, it is missing an additional condition on the equality of x.ip and t.ip.

If you only want the ip and date, then the outer query is not needed -- the subquery generates both those columns.

Personally, I would use a correlated subquery:

select uah.*
from user_access_history uah
where uah.access_date = (select max(uah2.access_date)
                         from user_access_history uah2
                         where uah2.ip = uah.ip
                        );

You can add the where clause to the outer query.

With an index on user_access_history(ip, access_date), this should be faster than the version suggests by your question.

Upvotes: 1

Related Questions