Home » SQL & PL/SQL » SQL & PL/SQL » Query for below scenario using analytical functions
Query for below scenario using analytical functions [message #584490] Tue, 14 May 2013 16:25 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Below is the format of data which I have along with expected output.

COL-1	COL-2
-------------------------
1-Jan-13	5
2-Jan-13	
3-Jan-13	
4-Jan-13	
5-Jan-13	10
6-Jan-13	
7-Jan-13	
8-Jan-13	
9-Jan-13	15
10-Jan-13	
 
 
Expected
-------------
 
COL-1	COL-2
---------------------------
1-Jan-13	5
2-Jan-13	5
3-Jan-13	5
4-Jan-13	5
5-Jan-13	10
6-Jan-13	10
7-Jan-13	10
8-Jan-13	10
9-Jan-13	15
10-Jan-13	15



Thanks in Advance
Re: Query for below scenario using analytical functions [message #584515 is a reply to message #584490] Wed, 15 May 2013 00:06 Go to previous messageGo to next message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Nothing analytical in my example.
SQL> select * from test order by col1;

COL1             COL2
---------- ----------
01.01.2013          5
02.01.2013          5
03.01.2013          5
04.01.2013          5
05.01.2013         10
06.01.2013         10
07.01.2013         10
08.01.2013         10
09.01.2013         15
10.01.2013         15

10 rows selected.

SQL> update test t set
  2    t.col2 = (select max(t1.col2)
  3              from test t1
  4              where t1.col2 is not null
  5                and t1.col1 <= t.col1
  6             );

10 rows updated.

SQL> select * from test order by col1;

COL1             COL2
---------- ----------
01.01.2013          5
02.01.2013          5
03.01.2013          5
04.01.2013          5
05.01.2013         10
06.01.2013         10
07.01.2013         10
08.01.2013         10
09.01.2013         15
10.01.2013         15

10 rows selected.

SQL>
Re: Query for below scenario using analytical functions [message #584517 is a reply to message #584490] Wed, 15 May 2013 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 58934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Query for below scenario using analytical functions [message #584518 is a reply to message #584515] Wed, 15 May 2013 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Littlefoot,

In your example, col2 is never null as in OP's example.
Are you sure this the MAX value? Is this not the LAST value?

Regards
Michel
Re: Query for below scenario using analytical functions [message #584522 is a reply to message #584518] Wed, 15 May 2013 00:47 Go to previous message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
LOL! Sorry, my bad! Forgot to ROLLBACK after testing which compromised my test case. Here's a "real" one (MAX is OK, I suppose).
SQL> select * from test order by col1;

COL1             COL2
---------- ----------
01.01.2013          5
02.01.2013
03.01.2013
04.01.2013
05.01.2013         10
06.01.2013
07.01.2013
08.01.2013
09.01.2013         15
10.01.2013

10 rows selected.

SQL> update test t set
  2    t.col2 = (select max(t1.col2)
  3              from test t1
  4              where t1.col2 is not null
  5                and t1.col1 <= t.col1
  6             );

10 rows updated.

SQL> select * from test order by col1;

COL1             COL2
---------- ----------
01.01.2013          5
02.01.2013          5
03.01.2013          5
04.01.2013          5
05.01.2013         10
06.01.2013         10
07.01.2013         10
08.01.2013         10
09.01.2013         15
10.01.2013         15

10 rows selected.

SQL>
Previous Topic: How to find table name and column name
Next Topic: PL/SQL: numeric or value error(How to use date as an date as input parameter)
Goto Forum:
  


Current Time: Fri Aug 29 07:00:50 CDT 2014

Total time taken to generate the page: 0.30862 seconds