CODEWITHSUNDEEP
mysqlsql
steeped
steeped

Reputation: 2633

MySQL: Select unique value in column based on another columns value

I have a table set up like this:

id    |      ip      |   name    
---------------------------------
1     | 54.34.32.222 |   John 
2     | 23.44.64.843 |   Rick 
3     | 54.34.32.222 |   John 
4     | 23.44.64.843 |   John 
5     | 14.432.45.45 |   Lisa 
6     | 54.34.32.222 |   Lisa 
7     | 14.432.45.45 |   Lisa

I only want to grab a unique IP per name. For example, "54.34.32.222" appears for John twice, so I only want to grab the first row. But "54.34.32.222" also appears for Lisa, so I would like to grab that IP as well.

The result should look something like this:

id    |      ip      |   name    
---------------------------------
1     | 54.34.32.222 |   John 
2     | 23.44.64.843 |   Rick 
4     | 23.44.64.843 |   John 
5     | 14.432.45.45 |   Lisa 
6     | 54.34.32.222 |   Lisa

How would you count the amount of times names appear? When doing so, it counts how many times the ip appears within the name, but I want the opposite.

SELECT MIN(id), COUNT(name), ip, name FROM yourTable GROUP BY ip, name

Upvotes: 1

Views: 72

Answers (4)

ernest_k
ernest_k

Reputation: 45309

You could use the following query, which selects the lexical minimum of the IP address for any given name:

SELECT NAME, MIN(IP) AS IP
FROM TABLENAME
GROUP BY NAME

If you need the IP address corresponding to the first record found for that name (ie, the one on the record with the lowest ID):

SELECT NAME, IP
FROM TABLENAME TN
WHERE ID = (
    SELECT MIN(ID)
    FROM TABLENAME TN1
    WHERE TN1.IP = TN.IP
    AND TN1.NAME = TN.NAME
)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

You never mentioned how you want to determine which record to retain in the case of duplicate ip-name pairs. However, based on your sample output it appears you are retaining the record with the smallest id value. In this case, we can just take the MIN(id) while grouping to get the desired result:

SELECT MIN(id), ip, name
FROM yourTable
GROUP BY ip, name

Follow the link below for a running demo:

SQLFiddle

Upvotes: 3

Mike Brant
Mike Brant

Reputation: 71384

You would likely need to do a join against a derived table here to get what you want. You could also do as subselect, but I will show join solution, as for most use case it would be likely to perform better.

SELECT
    yourtable.id AS id,
    ip,
    name
FROM yourtable
/* join regular table to a derived table
 * where you have selected the first row id for each user
 */
INNER JOIN (
    SELECT MIN(id)
    FROM yourtable
    GROUP BY name
) AS min_id_per_name
  ON yourtable.id = min_id_per_name.id
ORDER BY yourtable.id

Upvotes: 0

hacene abdessamed
hacene abdessamed

Reputation: 559

This should work for you

SELECT min(id),ip,name FROM youTable group by ip,name

Upvotes: 0

Related Questions