Retrieve rows based on multiple data patterns

Question

I have the following table data:

Qns: How do I retrieve only the rows highlighted in Yellow?

Thoughts: What I did try was to do a SUM(A), SUM(B), SUM(C) columns and group by ID_2 column. After which, filter using a WHERE clause on B=1 OR A=1 to retrieve the records. However, I am getting also getting the first record (A23,C1) too based on the select criteria. This is what I would like to avoid retrieving.

1) The first criteria is always to retrieve B=1 as priority for the same ID_2.

2) The second criteria is to retrieve those with A=1 only taking into account that of the first criteria. Hence, based on ID_2, if there is a valid B=1 records that has already been retrieved, it should not retrieve twice.

In this case, A34 is preferred rather than A23 (For instance, if based on ID_2, A or B both have 1, I would ONLY want the row whereby B=1. In addition, A67,A89 are retrieved too.

Appreciate the help in the generation of this SELECT statement.

Please elaborate on the logic for choosing those particular rows. After all, id_1 in ('A34', 'A67', 'A89') trivially accomplishes what you want. — Gordon Linoff
– Gordon Linoff, Commented Oct 30, 2015 at 18:17
Does this question apply to mysql or Oracle? It wouldn't apply to both. — Tripp Kinetics
– Tripp Kinetics, Commented Oct 30, 2015 at 18:17
Please explain the logic more clearly. You write twice "If based on ID_2" -- is that an error? — trincot
– trincot, Commented Oct 30, 2015 at 18:24
How are we supposed to tell you how to implement logic when you haven't explained the rules you want to enforce? I mean - why is A23 excluded by A67 included? If you can't express your rules clearly - you aren't likely to be able to code to them — Michael Broughton
– Michael Broughton, Commented Oct 30, 2015 at 19:52

trincot · Accepted Answer · 2015-10-30 18:36:22Z

1

I think this is what you want:

Select rows with B = 1
Select also rows with A = 1 provided that there is no other row for the same ID_2 that has B = 1

If so, then this can be accomplished with this SQL:

SELECT  ID_1, ID_2, A, B, C
FROM    tableName t1
WHERE   B = 1
    OR  (   A = 1 
        AND Not Exists (
                SELECT 1
                FROM   tableName t2
                WHERE  t1.ID_2 = t2.ID_2
                AND    B = 1)
        )

You can make an aggregated version as follows:

SELECT  ID_2, SUM(A), SUM(B), SUM(C)
FROM    tableName t1
WHERE   B = 1
    OR  (   A = 1 
        AND Not Exists (
                SELECT 1
                FROM   tableName t2
                WHERE  t1.ID_2 = t2.ID_2
                AND    B = 1)
        )
GROUP BY ID_2

edited Oct 30, 2015 at 18:36

answered Oct 30, 2015 at 18:31

trincot

357k38 gold badges282 silver badges339 bronze badges

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

1 Comment

Vamsi Prabhala Over a year ago

@trincot..you shouldn't waste time guessing what the op thinks..let him explain what he needs

Collectives™ on Stack Overflow

Retrieve rows based on multiple data patterns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related