CODEWITHSUNDEEP
mysqlsql
encore leet
encore leet

Reputation: 65

Finding unique column pairs

Let's say my table looks like this:

1. user 1.2.3.4
2. user 1.2.3.4
3. user 2.3.4.5
4. user 2.3.4.5
5. user 1.2.3.5
6. anotheruser 1.2.3.6
7. anotheruser 1.2.3.4

I want to get list of all ips with usernames. The query may look like this:

select username, ip from client_logs where ip is unique

I want output like this:

user 1.2.3.4
user 2.3.4.5
user 1.2.3.5
anotheruser 1.2.3.6
anotheruser 1.2.3.4

I already tried to do it by select distinct, but I have only ips, or ip - user. The query I've tried is:

SELECT DISTINCT ip, username
FROM client_logs
ORDER BY username

Upvotes: 2

Views: 51

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

USE GROUP BY

SQL Fiddle Demo

SELECT username, ip
FROM client_logs
GROUP BY username, ip
ORDER BY username, ip

Edit

I also try your first solution and works

Only distinct DEMO

SELECT distinct username, ip
FROM client_logs
ORDER BY username, ip

Upvotes: 3

encore leet
encore leet

Reputation: 65

I solved it by following query:

SELECT username, ip
FROM client_logs
GROUP BY ip
ORDER BY username, ip

Upvotes: -2

Related Questions