CODEWITHSUNDEEP
sqloracle-database
Angelo Pisaturo
Angelo Pisaturo

Reputation: 39

SQL_Oracle query

I need your precious help :-)

Imagine the ZAP_TEST_AP table composed of columns A1, B1 and C1 which contains the info shown in the image below

enter image description here

row number A1 B1 C1
1 PRIMARY 11111 host1
2 PRIMARY (null) host1
3 (null) (null) host1
4 PRIMARY 22222 host2
5 (null) 22222 host2
6 (null) (null) host2
7 PRIMARY (null) host3
8 (null) (null) host3
9 (null) 44444 host4
10 (null) (null) host4
11 (null) (null) host5

my goal is to make a query that is able to extract only the rows 1,4,7,9:

row number A1 B1 C1
1 PRIMARY 11111 host1
4 PRIMARY 22222 host2
7 PRIMARY (null) host3
9 (null) 44444 host4

that is to say:

  1. in the case in which (rows 1,2,3) for the same host1 (field c1) I have three rows where for one there are both a1 and b1, one both null and one where only a1 is set, I would like to extract only the one where both are present --> row 1

  2. in the case in which (rows 4,5,6) for the same host2 (field c1) I have three rows where for one there are both a1 and b1, one both null and one where only b1 is set, I would like to extract only the one where both are present --> row 4

  3. in the case in which (rows 7,8) for the same host3 (field c1) I have two rows where for one they are both null and one where only a1 is set, I would like to extract only the one where a1 is set --> row 7

  4. in the case in which (rows 9,10) for the same host4 (field c1) I have two rows where for one they are both null and one where only b1 is set, I would like to extract only the one where b1 is set --> row 9

  5. in case (row 11) for host5 (field c1) I have only one row and both a1 and b1 are null, I would like it not to be extracted

I hope I have clearly explained my problem :-(

Thanks in advance for your cooperation !!!

Upvotes: 0

Views: 58

Answers (3)

Mahamoutou
Mahamoutou

Reputation: 1555

You can also use this solution using decode function in the order by clause of row_number analytic function.

select row_number, A1, B1, C1
from (
  select row_number, A1, B1, C1
    , row_number()over(partition by C1 order by decode(A1, null, 0, 1) + decode(B1, null, 0, 1) desc, row_number ) rnb
    , max(decode(A1, null, 0, 1) + decode(B1, null, 0, 1))over(partition by C1)max_presence
  from ZAP_TEST_AP
)
where rnb = 1
and max_presence > 0
;

Upvotes: 0

user5683823
user5683823

Reputation:

To cover the cases I mentioned in my comments to your question (cases when more than one row may be shown in the output, for the same host), we need to use dense_rank() rather than row_number(). I would also write the case expression in a more user-friendly way, and filter out the rows where both a1 and b1 are null from the beginning; something like this:

select a1, b1, c1
from   (
         select a1, b1, c1,
                dense_rank() over ( partition by c1
                                    order by case when a1 is not null
                                                   and b1 is not null then 2
                                                                      else 1
                                                   end
                                             desc) as rnk
         from   zap_test_ap z
         where  a1 is not null or b1 is not null
       )
where  rnk = 1
;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can count the number of null values and take the first one using row_number():

select c1, c2, c3
from (select t.*,
             row_number() over (partition by c3
                                order by (case when c1 is not null then 1 else 0 end) +
                                         (case when c2 is not null then 1 else 0 end) desc
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 0

Related Questions