How do I select rows where elements of group have the same value?

I have a table like this:

 prop1 | prop2
 aaa   | xxx
 aaa   | xxx
 bbb   | yyy
 bbb   | !yyy
 ccc   | zzz
 ccc   | zzz
 ddd   | !kkk
 ddd   | kkk

and I want to be like:

 prop1 | prop2
 bbb   | yyy
 bbb   | !yyy
 ddd   | !kkk
 ddd   | kkk

How do I do in SQL (firebird)?

Solution 1:

If you want result of a select from the table to look like is written in your example, this may be the query:

select * from t t1 where exists
  (select * from t t2
    where t1.prop1 = t2.prop1 and t1.prop2 <> t2.prop2)

If you want (as topic say) select groups where all rows have the same values then the query is like this:

select prop1, MAX(prop2) from t
  group by prop1 having MAX(prop2) = MIN(prop2)

If you want your table after a query to look like in your example (i.e. delete groups that you would get from the query above) then it is

execute block as
declare p varchar(100);
begin
  for select prop1 from t
    group by prop1 having MAX(prop2) = MIN(prop2)
    into :p do
  begin
    delete from t where prop1 = p;
  end
end