Group by

Question

Hi,

I have table which has two columns, Name and IPAddress as follows

Name IpAddress
ABCD 10.2.1.231
ABCD 10.2.1.231
ABCD 10.2.1.232
EFGH 10.2.1.235

I need to separate IpAddress whose Name is not repeated.

Thanks in advance.

Ali

Answer 1

Writh a GROUP BY on Name with HAVING count(*) > 1 and JOIN it back to the original table.

Follow the example at this blog:

http://www.sqlusa.com/bestpractices2005/joinwithgroupby/

---

Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: SQL Programming & Database Design Using Microsoft SQL Server 2012

Answer 2

Your table and sample data make not that much sense. But you may try

SELECT O.*
FROM yourTable O
WHERE O.[Name] IN (
  SELECT I.[Name]
  FROM yourTable I
  WHERE I.IpAddress = O.Address
  GROUP BY I.[Name]
  HAVING Count(I.*) = 1
);