1

How can I retrieve an row with unique value pairs that occur more than once... Heres an example of what I'm what I am trying to..

SELECT 
 column_fk_id1, column_fk_id2,
 COUNT(distinct column_fk_id1, column_fk_id2) AS unique_pair_repeats
FROM table
WHERE column_fk_id1 IS NOT NULL
GROUP BY column_fk_id1, column_fk_id2
HAVING unique_pair_repeats > 2
  • 1
    What's "Msql"? What DBMS are you using? Sample data and expected output? – mustaccio Dec 22 '17 at 13:38
  • 1
    Does "MSQL" mean "MS SQL Server" or "MySQL"? – a_horse_with_no_name Dec 22 '17 at 14:43
  • Took a not so wild guess and added the MySQL tag. – ypercubeᵀᴹ Dec 22 '17 at 17:42
6

What you have almost works, just remove the distinct and change the > 2 to > 1. The distinct is not necessary as the grouping handles that and the > 2 is looking for things that have at least three entries rather than just two.

drop table tab1;
create table tab1 as (select 1 column_fk_id1,2 column_fk_id2 from dual);
insert into tab1 values (1,2);
insert into tab1 values (2,1);
insert into tab1 values (3,4);
insert into tab1 values (4,3);
insert into tab1 values (5,5);
insert into tab1 values (6,7);
insert into tab1 values (6,7);
insert into tab1 values (6,8);
commit;


SELECT column_fk_id1, column_fk_id2, COUNT(*) AS unique_pair_repeats
FROM tab1
WHERE column_fk_id1 IS NOT NULL
GROUP BY column_fk_id1, column_fk_id2
HAVING count(*) > 1;

COLUMN_FK_ID1 COLUMN_FK_ID2 UNIQUE_PAIR_REPEATS
------------- ------------- -------------------
            1             2                   2
            6             7                   2

2 rows selected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.