CODEWITHSUNDEEP
databasepostgresql
MaziN
MaziN

Reputation: 1

Postgresql select

My datatable:

[a] | [b]
----+----
1   |   1
1   |   2
1   |   3
2   |   1
2   |   2
3   |   1

What is the correct select for:

SELECT a FROM table WHERE b = 1 AND b = 2 AND b = 3 // Result = 1
SELECT a FROM table WHERE b = 1 AND b = 2 // Result = 2

EDIT: Thanks this query resolve my problem:

SELECT a FROM table WHERE b IN (1,2,3) AND a IN (SELECT a FROM table GROUP BY a HAVING count(*) = 3) GROUP BY a HAVING count(*) = 3 // Result = 1
SELECT a FROM table WHERE b IN (1,2) AND a IN (SELECT a FROM table GROUP BY a HAVING count(*) = 2) GROUP BY a HAVING count(*) = 2 // Result = 2

Upvotes: 0

Views: 46

Answers (2)

Vivek S.
Vivek S.

Reputation: 21915

Since OP lacks some info:

select a from (
select a,row_number() over(partition by a) rn from foo 
where b in (1,2,3) )t
where rn=(select count(a) from foo where a =1) -- you can use `rn` =3 instead of `select count(a) from foo where a =1`



select a from (
select a,row_number() over(partition by a) rn from foo 
where b in (1,2) )t
where rn=(select count(a) from foo where a =2)-- you can use `rn` =2 instead of `select count(a) from foo where a =2`

Upvotes: 0

Dmitri
Dmitri

Reputation: 9157

Not exactly clear what you're asking, but I think you're looking for EXISTS: http://www.postgresql.org/docs/9.4/static/functions-subquery.html

Depending on other constraints on your data, you may be able to do:

SELECT a FROM "table" WHERE b IN(1,2,3) GROUP BY a HAVING count(*) = 3

Upvotes: 1

Related Questions