Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t (
  column1 INTEGER,
  column2 INTEGER
);

INSERT INTO t
  (column1, column2)
VALUES
  (1, 1),
  (1, 2),
  (2, 1),
  (2, 1),
  (3, 3),
  (4, 5);

CREATE TABLE



INSERT 0 6


with ranked as (
    select *,
      dense_rank() over (partition by column1 order by column2) as dr 
    from t
),
maxed as (
    select *,
      max(dr) over (partition by column1) as max_dr
    from ranked
)
select
  column1,
  column2
from maxed
where max_dr > 1;


column1
column2


1
1

1
2



SELECT 2


with counted as (
    select *,
      count(*) over (partition by column1, column2) as c1,
      count(*) over (partition by column1) as c2
    from t
)
select
  column1,
  column2
from counted
where c1 < c2;


column1
column2


1
1

1
2



SELECT 2