1

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
Improve this question
0

4 Answers 4

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

you beat me also +1 for the example sqlfiddle
Thank you! 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
@steeped Could you update your question with the desired output you want for this modification?
@TimBiegeleisen Done!
0

This should work for you

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

4 Comments

Same as Tim Biegeleisen's answer, 3 minutes ago.
It is a coincidence
Hey, it was just a note that it's no point giving the same answer one more time.
if a saw his answer , i wouldn't post mine
0

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
Improve this answer

Comments

0

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
)
Improve this answer

8 Comments

That's not what was asked for though - what is required is to remove any duplicates & retaining the first unique record
I was already adding a second option, which picks the record with the lowest ID found for any given name. That's assuming the ID value reflects order of record insert.
The second option still doesn't work - it will filter out duplicate uses of the IP address - but OP wants to remove duplicates on both IP AND Name.
@PaulF: No, the question states "I only want to grab a unique IP per name.". And the example even has multiple entries with the name "John".
"But "54.34.32.222" also appears for Lisa, so I would like to grab that IP as well."
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.