Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    4

    Unanswered: SQL query to find duplicative (paired) columns

    Hi All,

    I have the following table:

    f_name f_country f_ID
    ABC US 123
    DEF GB 123
    ABC US 456
    GHI GB 789
    etc.

    I need to run a query to discover all instances where a f_name and f_country pair exists for more than one f_ID. ABC/US is one such example; IDs 123 and 456 have this pair.

    Hope it makes sense. Any help appreciated!

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    You need the ID's in the result, or only the columns and F_Name f_country?

  3. #3
    Join Date
    May 2012
    Posts
    4
    Hi -- preferably all three, but just the 'problematic' name+country pair would probably suffice. Thanks!

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    For name and country try:

    Code:
    select f_name, f_country
    from MyTable
    group by f_name, f_country
    having count(*) > 1

    For all columns try:

    Code:
    with CTE as
    (
        select t.*, count(*) OVER(PARTITION BY t.f_name, t.f_country) as Qty
        from MyTable
    )
    
    select * from CTE
    where Qty > 1
    Hope this helps.

  5. #5
    Join Date
    May 2012
    Posts
    4
    Sorry, should have mentioned that the duplicative name/country pairs must be from different IDs.

    I've updated the table below. Notice that DEF/GB appears twice, but for the same ID. This is fine -- and I don't want to get these in results. I only want cases like ABC/US which exists under two IDs.

    f_name f_country f_ID
    ABC US 123
    DEF GB 123
    DEF GB 123
    ABC US 456
    GHI GB 789
    etc.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT f_name
         , f_country
      FROM daTable
    GROUP 
        BY f_name
         , f_country
    HAVING COUNT(DISTINCT f_id) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2012
    Posts
    213
    For all columns try:

    Code:
    select * from MyTable as t
    where 
        exists 
            (select 1 from MyTable as s
             where (s.f_name = t.f_name) and (s.f_country = t.f_country)
             having count(distinct s.f_ID) > 1)
    Hope this helps.

  8. #8
    Join Date
    May 2012
    Posts
    4
    Thank you, imex and r937! This has helped me no end.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •