Postgresql: Return Unique Rows

postgresql

I'm using postgresql and I have a large database. A simplified version of the datastructure for my table looks like this:

id (primary unique key), data, userID
1, foo, 4
2, foobar, 4
3, lala, 5
4, boo, 6
etc...

I want to find all the rows where the userID value is unique in the entire table structure. So in the above example, it would return the rows for id's 3 and 4. If the userID is listed in two or more rows, I just want to discard it.

Is this possible in one query?

Thanks in advance.

Best Answer

select *
from the_table
where userid in (select userid
                 from the_table
                 group by userid
                 having count(*) = 1)

Alternatively:

select *
from the_table
   inner join (select userid
               from the_table
               group by userid
               having count(*) = 1) t on t.userid = the_table.userid;

or (possibly more efficient because only a single scan is needed):

select id, data, userid
from (
  select id, 
         data, 
         userid,
         count(*) over (partition by userid) as cnt
  from the_table
) t
where cnt = 1;
Related Question