Select all the minimun and maximun values of the table

Question

I have a SQL table like this:

pr_Key	fr_Key	attr_1	attr_2
p_1	f_1	100	150
p_2	f_1	150	200
p_3	f_1	200	250
p_4	f_2	100	150
p_5	f_2	150	200
p_6	f_2	200	250

But, I only want to select all the minimum and maximum for attr_1 and attr_2 of fr_Key just like this:

| pr_Key | fr_Key | attr_1 | attr_2 |
| ------ | ------ | ------ | ------ |
|   p_1  |   f_1  |  100   |  150   | <-- the minimum values of f_1
|   p_3  |   f_1  |  200   |  250   | <-- the maximum values of f_1
|   p_4  |   f_2  |  100   |  150   | <-- the minimum values of f_2
|   p_6  |   f_2  |  200   |  250   | <-- the maximum values of f_2

Answer 1

One method uses correlated subqueries in the where clause:

select t.*
from t
where t.attr1 = (select min(t2.attr1) from t t2 where t2.fr_Key = t.fr_Key) or
      t.attr1 = (select max(t2.attr1) from t t2 where t2.fr_Key = t.fr_Key) or
      t.attr2 = (select min(t2.attr2) from t t2 where t2.fr_Key = t.fr_Key) or
      t.attr2 = (select max(t2.attr2) from t t2 where t2.fr_Key = t.fr_Key);
  

EDIT:

Actually, I think you want the minimum and maximum across both columns. If so:

select t.*
from t
where least(t.attr1, t.attr2) =
          (select least(min(t2.attr1), min(t2.attr2))
           from t t2
           where t2.fr_Key = t.fr_Key
          ) or
      greatest(t.attr1, t.attr2) =
          (select greatest(max(t2.attr1), max(t2.attr2))
           from t t2
           where t2.fr_Key = t.fr_Key
          )

Answer 2

E.g.

SELECT DISTINCT a.*
  FROM my_table a
  JOIN 
     ( SELECT fr_key,1 attr, MIN(attr_1) val FROM my_table GROUP BY fr_key
       UNION
       SELECT fr_key,2, MIN(attr_2) val FROM my_table GROUP BY fr_key
       UNION
       SELECT fr_key,1 attr, MAX(attr_1) val FROM my_table GROUP BY fr_key
       UNION
       SELECT fr_key,2, MAX(attr_2) val FROM my_table GROUP BY fr_key
     ) b
    ON b.fr_key = a.fr_key
   AND CASE WHEN attr = 1 THEN b.val = attr_1 ELSE b.val = attr_2 END
;