Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: how to do this with sql?
"hopehope_123" <hopehope_123_at_yahoo.com> a écrit dans le message de news: 1147272051.477285.314240_at_u72g2000cwu.googlegroups.com...
| Hi ,
|
| I have to do the following :
|
| table_a has set of records based on the entry date:
|
| (entry_date,name,surname,customer_id) :
|
| 20060510,null,yy,1
| 20060509,xx,null,1
|
| What i need is to combine these two rows into 1 , the final record will
| be:
|
| xx,yy,1 ( entry_date is not needed in final record) .
|
| The rule for each column is : get the first non-null value which is
| entered last.
|
| How can i do this? I tried some analytic functions but could not
| succeed.
|
| By using seperate sql statements i can do the following:
|
| SELECT name
| FROM table_a
| where name is not null and entry_date =
| (select max(entry_date)
| from table_a where name is not null)
|
| SELECT surname
| FROM table_a
| where surname is not null and entry_date =
| (select max(entry_date)
| from table_a where surname is not null)
|
| Is it possible to do this in a single sql statement?
|
| Kind Regards,
| tolga
|
SQL> with data as (
2 select '20060510' dt, null col1, 'yy' col2, 1 col3 from dual
3 union all
4 select '20060509' dt, 'xx' col1, null col2, 1 col3 from dual
5 )
6 select distinct
7 first_value(col1 ignore nulls) 8 over (order by dt desc 9 rows between unbounded preceding and unbounded following) col1, 10 first_value(col2 ignore nulls) 11 over (order by dt desc 12 rows between unbounded preceding and unbounded following) col2, 13 first_value(col3 ignore nulls) 14 over (order by dt desc 15 rows between unbounded preceding and unbounded following) col316 from data
1 row selected.
Regards
Michel Cadot
Received on Wed May 10 2006 - 11:53:17 CDT
![]() |
![]() |