CODEWITHSUNDEEP
postgresqlgroup-by
labrynth
labrynth

Reputation: 155

Select rows that satisfy a certain group condition in psql

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.

Upvotes: 3

Views: 7224

Answers (3)

Michas
Michas

Reputation: 9428

It looks it is not possible to use window function in WHERE or HAVING. Below is solution based on JOINs.

  1. JOIN every row with all rows of the same id.
  2. Filter based on second set.
  3. Show result from first set.

The SQL looks like this.

SELECT a.*
FROM a_table AS a
INNER JOIN a_table AS value ON a.id = b.id
WHERE b.value = 0;

Upvotes: 0

Rémy Baron
Rémy Baron

Reputation: 1399

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

Upvotes: 2

jmelesky
jmelesky

Reputation: 3970

If I'm understanding correctly, it's a fairly simple having clause:

=# SELECT id, MIN(value), MAX(value) FROM foo GROUP BY id HAVING MIN(value) = 0;
 id | min | max 
----+-----+-----
  1 |   0 |   3
  3 |   0 |   2
(2 rows)

Did I miss something that is making it more complicated?

Upvotes: 0

Related Questions