1

Topic: Choice of a line with the greatest quantity not zero columns

Good afternoon all.
It is necessary to select from the table a line with the greatest quantity not zero columns.
From an example the last line should be more low selected.
While made such variant (with one more  which selects the first line).
Whether there is more simple/beautiful variant?
In advance thanks.

with sel as
(select ' a ' a, null b, null c, ' d ' d, null e
union all
select null a, null b, null c, ' d ' d, null e
union all
select ' a ' a, ' b ' b, null c, ' d ' d, null e
union all
select ' a ' a, null b, ' c ' c, ' d ' d, ' e ' e)
select *
from sel s
order by decode (a, null, 0,1) + decode (b, null, 0,1) + decode (b, null, 0,1) + decode (d, null, 0,1) + decode (e, null, 0,1) desc

2

Re: Choice of a line with the greatest quantity not zero columns

Whether

order_by_not_null wrote:

There is more simple/beautiful variant?

The bad task should not have beautiful decisions.

3

Re: Choice of a line with the greatest quantity not zero columns

Whether

order_by_not_null wrote:

There is more simple/beautiful variant?

nvl2

4

Re: Choice of a line with the greatest quantity not zero columns

Elic wrote:

it is passed...

nvl2

Still to consider that such lines of m. More than one

5

Re: Choice of a line with the greatest quantity not zero columns

[quote = __ vvp _] it is passed...
Still to consider that such lines of m. More than one

So you should select a line with the maximum number of empty fields or to sort by an amount of empty fields?
If the first that is possible so:

with sel as
(select ' a ' a, null b, null c, ' d ' d, null e
from dual
union all
select null a, null b, null c, ' d ' d, null e
from dual
union all
select ' a ' a, ' b ' b, null c, ' d ' d, null e
from dual
union all
select ' a ' a, null b, ' c ' c, ' d ' d, ' e ' e
from dual),
sel1 as
(select s.*
, nvl2 (a, 0,1) + nvl2 (b, 0,1) + nvl2 (c, 0,1) + nvl2 (d, 0,1) + nvl2 (e, 0,1) sum_col
, max (nvl2 (a, 0,1) + nvl2 (b, 0,1) + nvl2 (c, 0,1) + nvl2 (d, 0,1) + nvl2 (e, 0,1)) over (partition by 1) max_sum_col
from sel s)
select *
from sel1
where sum_col = max_sum_col