Postgresql select

Question

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

The question needs an exact table definition, your version of Postgres and a more descriptive title. Can columns be NULL? Is (a,b) unique? The table definition tells us all that and more: what you get with \d tbl in psql. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 20, 2015 at 1:59

Dmitri · Accepted Answer · 2015-03-20 01:34:44Z

1

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

answered Mar 20, 2015 at 1:34

Dmitri

9,1675 gold badges39 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vivek S. · Accepted Answer · 2015-03-20 04:52:47Z

0

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`

answered Mar 20, 2015 at 4:52

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

Postgresql select

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related