Select rows that satisfy a certain group condition in psql

Question

Given the following table:

 id  |  value 
  ---+---------
  1  |    1 
  1  |    0
  1  |    3
  2  |    1
  2  |    3
  2  |    5
  3  |    2 
  3  |    1
  3  |    0
  3  |    1

I want the following table:

 id  |  value 
  ---+---------
  1  |    1 
  1  |    0
  1  |    3
  3  |    2 
  3  |    1
  3  |    0
  3  |    1

The table contains ids that have a minimum value of 0. I have tried using exist and having but to no success.

Answer 1

try this :

select * from foo where id in (SELECT id FROM foo GROUP BY id HAVING MIN(value) = 0) 

or that ( with window functions)

select * from 
 (select *,min(value) over (PARTITION BY id) min_by_id from foo) a 
where min_by_id=0