OracleBrains.Com header image 2
← Deleting duplicate row in certain condition using LEAD an analytic function! PL/SQL::New FOLLOWS Clause →

Interesting SQL

September 3rd, 2007 by Rajender Singh · 7 Comments

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.


Tags: Interesting Coding Showcase · SQL and PL/SQL

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