Первое не-null значение или null- если все null-евые

AndrewS

Дата: 01.06.2006 19:09:30

Уже мозг дымится... Хочется попроще и без pl/sql...

Дано:

create table demo
( id int
, seq int
, val int
);

insert into demo (id,seq,val) values( 1, 1, null);
insert into demo (id,seq,val) values( 1, 2, 102);
insert into demo (id,seq,val) values( 1, 3, 103);
insert into demo (id,seq,val) values( 2, 1, 201);
insert into demo (id,seq,val) values( 2, 2, 202);
insert into demo (id,seq,val) values( 2, 3, 203);
insert into demo (id,seq,val) values( 3, 1, null);
insert into demo (id,seq,val) values( 3, 2, null);
insert into demo (id,seq,val) values( 3, 3, null);

Хочется:

ID | FIRST_NOT_NULL
---|--------
1  | 102
2  | 201
3  | null

спасибо!

orawish

Дата: 01.06.2006 19:14:51

select id,nvl(to_char(min(val)),'null') from demo group by id order by id

dmidek

Дата: 01.06.2006 19:21:06

orawish

select id,nvl(to_char(min(val)),'null') from demo group by id order by id

Я уподоблюсь человеку, объясняющему смысл анекдота,
но все таки скажу, что наверное все таки хотелось простого

select id, min(val) from scott.demo group by id order by id

Elic

Дата: 01.06.2006 19:21:54

select id, min(val) keep (dense_rank first order by decode(val, null, 2, 1), seq) as first_not_null
  from demo
  group by id

andrey_anonymous

Дата: 01.06.2006 19:22:24

Не, мужики, это в молоко :)
Ибо "первое order by seq".

SQL> with demo as(
select 1 id, 1 seq, to_number(null) val from dual union all
select 1, 2, 102  from dual union all
select 1, 3, 103  from dual union all
select 2, 1, 201  from dual union all
select 2, 2, 202  from dual union all
select 2, 3, 203  from dual union all
select 3, 1, null from dual union all
select 3, 2, null from dual union all
select 3, 3, null from dual
)
select id, min(val) keep (dense_rank first order by decode(val,null,1,0),seq) first_not_null
from demo
group by id;

        ID FIRST_NOT_NULL
---------- --------------
         1            102
         2            201
         3 

SQL>

neodiX

Дата: 01.06.2006 19:24:50

2 AndrewS: на будующее, узнай для чего надо nulls last/first в order by...

AndrewS

Дата: 01.06.2006 19:26:28

спасибо!
тогда чуть усложню: VAL - строковое :(

neodiX

Дата: 01.06.2006 19:26:51

просто как вариант:

select id,nvl(to_char(min(val)),'null')  from demo group by id order by 1  nulls last;

andrey_anonymous

Дата: 01.06.2006 19:27:39

AndrewS

спасибо!
тогда чуть усложню: VAL - строковое :(

И?

with demo as(
select 1 id, 1 seq, to_char(null) val from dual union all
select 1, 2, '102'  from dual union all
select 1, 3, '103'  from dual union all
select 2, 1, '201'  from dual union all
select 2, 2, '202'  from dual union all
select 2, 3, '203'  from dual union all
select 3, 1, null from dual union all
select 3, 2, null from dual union all
select 3, 3, null from dual
)
select id, min(val) keep (dense_rank first order by decode(val,null,1,0),seq) first_not_null
from demo
group by id;

        ID FIRST_NOT_NULL
---------- --------------
         1 102
         2 201
         3 

SQL>

andrey_anonymous

Дата: 01.06.2006 19:29:48

neodiX

просто как вариант:

select id,nvl(to_char(min(val)),'null')  from demo group by id order by 1  nulls last;

Мимо :)

with demo as(
select 1 id, 1 seq, to_char(null) val from dual union all
select 1, 2, '103'  from dual union all
select 1, 3, '102'  from dual union all
select 2, 1, '203'  from dual union all
select 2, 2, '202'  from dual union all
select 2, 3, '201'  from dual union all
select 3, 1, null from dual union all
select 3, 2, null from dual union all
select 3, 3, null from dual
)
select id,nvl(to_char(min(val)),'null')  from demo group by id order by 1  nulls last;

        ID NVL(TO_CHAR(MIN(VAL)),'NULL')
---------- -----------------------------
         1 102
         2 201
         3 null