Re: how to do this with sql?

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 10 May 2006 18:53:17 +0200
Message-ID: <44621a7d$0$296$626a54ce@news.free.fr>

"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) col3

 16 from data
 17 /
CO CO COL3
-- -- ----------
xx yy 1

1 row selected.

Regards
Michel Cadot Received on Wed May 10 2006 - 11:53:17 CDT