CODEWITHSUNDEEP
mysql
georaldc
georaldc

Reputation: 1940

MySQL group by a column depending on values from another column

I was wondering how to achieve such a query. Say for example I have the following table which tracks clicks on links coming from different ip addresses:

id      link       ip
1       Link 1     100.100.1.1
2       Link 1     100.100.1.2
3       Link 2     100.100.1.3
4       Link 1     100.100.1.1
5       Link 3     100.100.1.4
6       Link 3     100.100.1.1

What I want to do is get all unique ip clicks to the same link, so the result should look like:

id      link       ip
1       Link 1     100.100.1.1
2       Link 1     100.100.1.2
3       Link 2     100.100.1.3
5       Link 3     100.100.1.4
6       Link 3     100.100.1.1

I'm thinking I should group the table but doing so will remove duplicates from either the link or ip column. Is it possible to just like group all Link 1 IPs, Link 2, IPs, Link 3 IPs etc.

Hopefully that didn't sound too confusing.

Upvotes: 0

Views: 174

Answers (2)

exussum
exussum

Reputation: 18578

select distinct would also work if you just wish you view the columns

no need for group

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can do this using group by:

select min(id) as id, link, ip
from t
group by link, ip;

In MySQL, you can also use this syntax:

select id, link, ip
from t
group by link, ip;

where id is in the select list but not in the group by. Although allowed, I think this is a bad habit to get into in general (there are a few situations where this feature is useful).

Upvotes: 1

Related Questions