Today I came across another interesting solution.
Situation was as follows:
There is PROJECTS table, with columns as follows (datatype never mind):
PERSON
ID_STATE
We need to select persons from this table having all the ID_STATE (2, 3 and 4)
I found solution given by Aketi Jyuuzou very interesting and simple!
SELECT person
FROM projects
GROUP BY person
HAVING MAX(case when ID_STATE = 2 then 1 else 0 end)
* MAX(case when ID_STATE = 3 then 1 else 0 end)
* MAX(case when ID_STATE = 4 then 1 else 0 end)= 1;
Which shows very good use of boolean algebra in having clause.
7 responses so far ↓
1 DJ // Sep 3, 2007 at 8:58 pm
Cool! Beats three scans over the table/index and a sort(union).
I would probably add a where predicate to filter ID_STATE anyway just in case.
2 Eugene // Sep 4, 2007 at 12:11 am
I think the following is a bit shorter and easier:
——————————————————————
select person
from projects
group by person
having min( (case when id_state in (2,3,4) then 1 else 0 end) ) = 1;
——————————————————————
Eugene
3 anonymous // Sep 4, 2007 at 2:53 pm
Cool !!!
that solution in
http://forums.oracle.com/forums/thread.jspa?threadID=549338
4 Jason Bucata // Sep 5, 2007 at 1:24 am
I’ve also done something similar to:
select person
from projects
where id_state in (2,3,4)
group by person
having count(distinct id_state) = 3
I just looked at the forum thread linked to above, and somebody else did the same thing.
5 Rajender Singh // Sep 5, 2007 at 10:27 am
Hi Eugene,
Only problem that I can see in your query is that if lets suppose a person is having id_state 2,3,4 and 5, then it will be not be selected.
Raj
6 rajs // Sep 5, 2007 at 10:38 am
Hi Jason,
Your one looks cool!
But haven’t checked from performace point of view. The way it looks like, it may be little bit slow!
Raj
7 Ray DeBruyn // Dec 7, 2007 at 2:29 am
Jason’s query is the only one that is filtering before grouping. Even if there is no index on id_state, the sorting for the grouping will be less.
The only issue I see is if there is a possibility of more than one record per person and is_state in the projects table.
Another solution would be:
SELECT person FROM projects WHERE id_state = 2
INTERSECT
SELECT person FROM projects WHERE id_state = 3
INTERSECT
SELECT person FROM projects WHERE id_state = 4
OR
SELECT a.person
FROM
(SELECT person FROM projects WHERE id_state = 2) a
, (SELECT person FROM projects WHERE id_state = 3) b
, (SELECT person FROM projects WHERE id_state = 4) c
WHERE a.person = b.person
AND b.person = c.person
Leave a Comment