Mysql Query: Display all unique IPs for a given user ID

Question

I am trying to get all the ip of the user have used till now.

"SELECT * FROM `logfeeds` WHERE uid=3"

simple query gives all the ips but what i want is to get each ip only one time.

logfeeds

I want the query to return each IP only once per uid.

The screenshot has both id = 23 and id = 29, but it should show only id = 23, As id=23 and id=29 have the same ip.

Answer 1

Use distinct:

From mysql doc:

DISTINCT specifies removal of duplicate rows from the result set.

SELECT distinct ip FROM logfeeds WHERE uid=3;

Answer 2

Use the following query :

"SELECT * FROM `logfeeds` WHERE uid=3 GROUP BY ip"

This will surely work for you.

Answer 3

If you want just the IP once each time, use DISTINCT.

SELECT DISTINCT ip, * FROM `logfeeds` WHERE uid=3;

If you care about the most common IPs then use GROUP BY

SELECT *, COUNT(ip) as logged_in_count FROM `logfeeds` WHERE uid=3 GROUP BY ip ORDER BY logged_in_count DESC;

While GROUP BY works when you need the count, it's better to use distinct in other cases as it is the more correct method.

See: How to select unique records by SQL

Answer 4

This assumes that id and timestamp increase in step
And that you want all columns (you did use SELECT *)

SELECT
   MIN(id), uid, ip, MIN(timestamp)
FROM
   logfeeds
WHERE
   uid=3
GROUP BY
   uid, ip

Answer 5

You should try the distinct statement

SELECT DISTINCT(ipcolumn) FROM logfeeds ....

More information: http://www.w3schools.com/sql/sql_distinct.asp

Answer 6

try this:

"SELECT Distinct(ip),timestamp FROM `logfeeds` WHERE uid=3"