mig33 SQL (1)

gistfile1.sql

-- Write a sql query that will return a list of duplicate phone numbers and the duplication count
  select PhoneNumber, count(*) count
  from   TheTable
  group by PhoneNumber
  having count(*) > 1
  ;

-- Write a sql query that will return a list of IDs with duplicate phone numbers
  select ID
  from   TheTable
  where  PhoneNumber in (select PhoneNumber from TheTable group by PhoneNumber having count(*) > 1)
  ;

-- Write a sql query that will return a list of IDs with no duplicate phone numbers using an outer join with the result from 1.
  select ID
  from   TheTable t1
  left outer join (select PhoneNumber, count(*) count
                   from   TheTable
                   group by PhoneNumber
                   having count(*) > 1)
  on t1.PhoneNumber = t2.PhoneNumber
  where t2.PhoneNumber is null
  ;