How to write SQL query that selects distinct pair values for specific criteria?

This problem has obviously been bothering me. The following appears to implement your logic, keeping arrays of visited values in rows:

with recursive t(t, p, score) as (    (values ('t1','p1',65),           ('t1','p2',60),           ('t1','p3',20),           ('t2','p1',60),           ('t2','p2',59),           ('t2','p3',15)     )),     cte(t, p, score, cnt, lastt, lastp, ts, ps) as (        (select t.*, count(*) over ()::int, tt.t, tt.p, ARRAY[tt.t], ARRAY[tt.p]         from t cross join              (select t.* from t order by score desc limit 1) tt        )         union all        select t, p, score,                sum(case when not (ts @> ARRAY[t] or ps @> ARRAY[p]) then 1 else 0 end) over ()::int,               first_value(t) over (order by case when not (ts @> ARRAY[t] or ps @> ARRAY[p]) then score end desc nulls last),               first_value(p) over (order by case when not (ts @> ARRAY[t] or ps @> ARRAY[p]) then score end desc nulls last),               ts || first_value(t) over (order by case when not (ts @> ARRAY[t] or ps @> ARRAY[p]) then score end desc nulls last),               ps || first_value(p) over (order by case when not (ts @> ARRAY[t] or ps @> ARRAY[p]) then score end desc nulls last)        from cte         where cnt > 0       ) select * from cte where lastt = t and lastp = p and cnt > 0;

It is relatively simple using the stored function:

--drop function if exists f();--drop table if exists t;create table t(x text,y text, z int);insert into t values  ('t1','p1',65),  ('t1','p2',60),  ('t1','p3',20),  ('t2','p1',60),  ('t2','p2',59),  ('t2','p3',15)/*,  ('t3','p1',20),  ('t3','p2',60),  ('t3','p3',40)*/;create function f() returns setof t immutable language plpgsql as $$declare  ax text[];  ay text[];  r t;begin  ax := '{}'; ay := '{}';  loop    select * into r      from t      where x <> all(ax) and y <> all(ay)      order by z desc, x, y limit 1;    exit when not found;    ax := ax || r.x; ay := ay || r.y;    return next r;  end loop;end $$;select * from f();╔════╤════╤════╗║ x  │ y  │ z  ║╠════╪════╪════╣║ t1 │ p1 │ 65 ║║ t2 │ p2 │ 59 ║╚════╧════╧════╝

However if uncomment the third bunch of the values the result will be different:

╔════╤════╤════╗║ x  │ y  │ z  ║╠════╪════╪════╣║ t1 │ p1 │ 65 ║║ t3 │ p2 │ 60 ║║ t2 │ p3 │ 15 ║╚════╧════╧════╝

Upd: and the equivalent using recursive CTE on the same test data:

with recursive r as (  (select x, y, z, array[x] as ax, array[y] as ay from t order by z desc, x, y limit 1)  union all  (select t.x, t.y, t.z, r.ax || t.x, r.ay || t.y from t, r   where not (t.x = any(r.ax) or t.y = any(r.ay))   order by t.z desc, t.x, t.y limit 1))select * from r;

t1 was used so you've excluded (t1,p2) but p1 was also used and you haven't excluded it. For me it looks like just groupping by first column.

select t1.c1, t2.c2, t1.s   from table1 t2   inner join (select c1, max(score) s from table1 group by t1) t1     on (t1.s=t2.score and t1.c1=t2.c1);

Where table1 is name for your table and c1 is first, c2 second and score third column;