0

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 !!!

Improve this question
4
  • Most people here want sample table data and the expected result as formatted text, not as images. Commented Aug 31, 2021 at 11:52
  • What if for the same host you have two distinct rows, both of which have non-null values in all columns? Which row do you want to pick then? Similarly, if for another host the a1 value is always null, the b1 value is always non-null, but there are several rows (for the same host, all with a1 being null), with distinct, non-null values for b1? Which particular row from those rows do you want to pick? Commented Aug 31, 2021 at 12:03
  • What if, for a particular host, there are two rows: in one, a1 is non-null and b1 is null, and in the other a1 is null and b1 is non-null? Which of these rows do you pick then? Commented Aug 31, 2021 at 12:05
  • thanks @mathguy for your questions. all lines that meet the conditions, then to answer your questions --> both (or more) rows in all three cases you asked Commented Aug 31, 2021 at 13:12

3 Answers 3

Reset to default
1

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
;
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thank you all for the help you have provided me
0

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;
Improve this answer

1 Comment

If for a given host, all rows have both a1 and b1 as null, the OP doesn't want anything for that host in the output. See host5 in his sample data and output. I don't see where you are handling that in your query. (Note also the further clarification in comments under his question.)
0

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
;
Improve this answer

2 Comments

If for a given host, all rows have both a1 and b1 as null, the OP doesn't want anything for that host in the output. See host5 in his sample data and output. I don't see where you are handling that in your query. (Note also the further clarification in comments under his question.)
Thansk @mathguy for pointing out this last condition. I hadn't got it before. Now, I made my answer to cover it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.