return column data not used in SQL HAVING query

Question

I think this should be straightforward, but I'm at a loss. I want to return data in the columns that weren't used in the SQL statement HAVING clause without having to do a 2nd query. I'm using PostgreSQL, but this should be a fairly generic question. Here's the data:

CREATE TABLE sandbox (
    ret_val character(10),
    key1 character(10),
    key2 character(10),
    value1 real,
    value2 real
);



COPY sandbox (ret_val, key1, key2, value1, value2) FROM stdin;
baa         DEF         Book        0.800000012 0.270000011
oink        GHI         Play        0.200000003 0.280000001
Oink        DEF         Book        0.300000012 0.109999999
chirp       GHI         Play        0.100000001 0
woof        DEF         Play        0.400000006 0.300000012
meow        DEF         Play        0.699999988 0.219999999
woof        ABC         Book        0.140000001 0.939999998
baa         ABC         Play        0.25999999  0.75
meow        ABC         Play        0.75999999  0.150000006
neigh       DEF         Movie       0.970000029 0.349999994
cluck       DEF         Movie       0.870000005 0.550000012
quack       GHI         Movie       0.870000005 0.119999997
\.

I want to return the values in (ret_val, value1, value2) ONLY for cases where the (key1,key2) pair is unique. So first I do a SELECT using HAVING:

=> SELECT key1,key2 from sandbox group by 1,2 HAVING count(*)=1;
    key1    |    key2    
------------+------------
 GHI        | Movie     
 ABC        | Book      
(2 rows)

to see that there are two unique rows. Then I select the data I need using the output of the HAVING:

=> SELECT ret_val,value1,value2 from sandbox where 
    (key1='ABC' and        key2='Book') OR (key1='GHI' and key2='Movie');
  ret_val   | value1 | value2 
------------+--------+--------
 woof       |   0.14 |   0.94
 quack      |   0.87 |   0.12
(2 rows)

Surely that should be done in a single query, right?

Answer 1

I would use a window function for this:

select ret_val, value1, value2
from (select s.*, count(*) over (partition by key1, key2) as cnt
      from sandbox
     ) s
where cnt = 1;

As a note, you can also do this with aggregation, but I don't think the query is as clean:

select max(ret_val) as ret_val, max(value1) as value1, max(value2) as value2
from sandbox
group by key1, key2 
having count(*) = 1;

The logic here is that if there is only one row in the group, then max() returns the value in that row.

Answer 2

Here is a join version:

SELECT ret_val,value1,value2 from sandbox,
      ( SELECT key1,key2 from sandbox group by 1,2 HAVING count(*)=1 ) as keys
    where sandbox.key1 = keys.key1 and sandbox.key2 = keys.key2

Answer 3

select 
    min(ret_val) as ret_val,
    min(val1) as val1, 
    min(val2) as val2
from sandbox 
group by key1, key2
having count(*) = 1;