CREATE TABLE TABLE1
(
C1 VARCHAR2(3), C2 VARCHAR2(3), C3 VARCHAR2(4)
);
INSERT INTO TABLE1 VALUES('A', '0', '1234');
INSERT INTO TABLE1 VALUES('A', '1', '4568');
INSERT INTO TABLE1 VALUES('A', '2', '5432');
INSERT INTO TABLE1 VALUES('B', '0', '3562');
INSERT INTO TABLE1 VALUES('B', '1', Null);
INSERT INTO TABLE1 VALUES('B', '2', Null);
INSERT INTO TABLE1 VALUES('C', '0', '2132');
INSERT INTO TABLE1 VALUES('C', '1', Null);
INSERT INTO TABLE1 VALUES('C', '2', '5431');
When you execute above query, we get the data into TABLE1 and each unique value of C1 column corresponds to 3 lines i.e. 0,1,2 (in C2 column). What is the query to select the data of column1 having its first line i.e. 0 in column2 has a value(which is not null) in C3 and all other lines of C2 have a value as Null in C3.
The answer for above example is
C1 C2 C3
------------------
B 0 3562
B 1 Null
B 2 Null
There are various rows. For each unique value of C1 can have multiple lines i.e. 0 to 100 etc. in C2 but I have taken above one as an example. In above you can see that A has values in all the 3 lines. B has value in 0th line but as Null in all other lines. C has values in 0th line and 2nd line but Null in 1st line. We need to select the unique value of C1 having value in 0th line and Null in other lines
Perhaps something like this:
select *
from table1
where c1 in (
select c1
from table1
group by c1
having count(case when c2 = 0 then c3 end) = 1
and count(c3) = 1
)
order by c1, c2
;
C1 C2 C3
--- --- ----
B 0 3562
B 1
B 2
This reads the base data twice. If you use analytic functions instead, you can have the base data read just once, but analytic functions themselves are slower than traditional aggregation. If this query works for you, but it is slow, you can try the analytic functions approach just to make sure, but I expect it will be slower, not faster.
Is column c2 supposed to be numeric? I treated it as such, but in your sample data you gave it as strings.