Home » SQL & PL/SQL » SQL & PL/SQL » ROWNUMBER () OVER Partition in Oracle 9i (Oracle 9i)
ROWNUMBER () OVER Partition in Oracle 9i [message #389846] Tue, 03 March 2009 15:03 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I have tried to search on forum but couldn't get it to work my below query.
I have 11 columns and for some of the 9 columns its results is common except last 10th amd 11th column.
I am tried to GROUP by and Partition but no luck.
Your advice will be thankful.

I tried following query:
SELECT * from (SELECT  DISTINCT s_name ,login, f_name, l_name, email, 
(SELECT d.login FROM user d WHERE d.id = c.R_id) "S_ID",
(SELECT d.email FROM User d WHERE d.id = c.R_id) "S_EMAIL",
--S_ID, S_EMAIL,
desc, wave,C_CODE, S_TASK, ROW_NUMBER()
over (PARTITION BY  b.S_name, d.login, d.f_name,d.l_name, d.email,
(SELECT d.login FROM user d WHERE d.id = c.rep_to_id) ,
(SELECT d.email FROM user d WHERE d.id = c.rep_to_id) ,
e.desc, f.wave ORDER BY b.S_name DESC
     ) AS rn
     FROM CT_E_STAK_MAP  	A,
          ct_stak 		B,
          e_info 		C,
          user 			D,
          e_code 		E,
          e_web_info 		F, 
          C_STAK_MAP 		G,
          C_CORS 		H, 
          C_S_TASK 		I,
          C_S_STAK_MAP 		J
WHERE a.C_stak_id 	= b.c_stak_id 
AND   b.c_stak_id 	= G.c_stak_id
AND   B.C_STAK_ID 	= J.C_STAK_ID
AND   J.C_S_TASK_ID 	= I.C_S_TASK_ID
AND   G.C_CORS_ID      	= H.C_CORS_ID
AND   a.U_id         	= c.U_id 
AND   a.U_id         	= d.id 
AND   d.id              = f.U_id 
AND   c.ctll3 		= e.c_st_val 
AND   c.ctl1_2 		= e.ctl1_2 
AND   e.c_set_name 	= 'EY' 
AND   c.active		= 1
ORDER BY S_NAME)

Now in my Select, first 9 columns are data are same for same 9 values but only different for column C_CODE and S_TASK.
How can i avoid that multiple recordsets?

Ex. Data:
s_name1	login1	f_name1	l_name1	email1	login1	S_ID1	S_EMAIL1	desc1	wave1	C_CODE1	S_TASK1
s_name1	login1	f_name1	l_name1	email1	login1	S_ID1	S_EMAIL1	desc1	wave1	C_CODE2	S_TASK2
s_name1	login1	f_name1	l_name1	email1	login1	S_ID1	S_EMAIL1	desc1	wave1	C_CODE3	S_TASK3
s_name1	login1	f_name1	l_name1	email1	login1	S_ID1	S_EMAIL1	desc1	wave1	C_CODE4	S_TASK4
...
...
...
s_name2	login2	f_name2	l_name2	email2	login2	S_ID2	S_EMAIL2	desc2	wave2	C_CODE1	S_TASK1
s_name2	login2	f_name2	l_name2	email2	login2	S_ID2	S_EMAIL2	desc2	wave2	C_CODE2	S_TASK2
s_name2	login2	f_name2	l_name2	email2	login2	S_ID2	S_EMAIL2	desc2	wave2	C_CODE2	S_TASK2



I need to avoid this first 9 columns to repeat each time except c-_code and S_task

Reult are coming from lultiple tables and joining multiple tables and this is the similar code which i have posted
for dynamic sql but i have to send user another results and i am not getting either using GROUP BY or ROWNUM () OVER Partition By function.


Thanks as always for your great help!
Re: ROWNUMBER () OVER Partition in Oracle 9i [message #389901 is a reply to message #389846] Wed, 04 March 2009 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In SQL*Plus, use BREAK command.

Regards
Michel
Re: ROWNUMBER () OVER Partition in Oracle 9i [message #390056 is a reply to message #389901] Wed, 04 March 2009 14:53 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Michel.
I am using sql*plus but i need to through output in excel for user so i think i can't use BREAK, right?

Thanks,

Re: ROWNUMBER () OVER Partition in Oracle 9i [message #390097 is a reply to message #390056] Wed, 04 March 2009 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But you can use Excel cell conditional formatting.

Regards
Michel
Re: ROWNUMBER () OVER Partition in Oracle 9i [message #390142 is a reply to message #390097] Thu, 05 March 2009 02:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's doable in SQL, but it's not pretty:
create table test_130 (col_1 number, col_2 number, col_3 number);

insert into test_130 values (1,1,2);
insert into test_130 values (1,1,3);
insert into test_130 values (1,1,4);
insert into test_130 values (1,2,1);
insert into test_130 values (1,2,2);
insert into test_130 values (1,2,3);
insert into test_130 values (2,1,1);
insert into test_130 values (2,1,2);

select case when col_1 = lag(col_1) over (order by col_1,col_2) then null else col_1 end as col_1
      ,case when col_2 = lag(col_2) over (order by col_1,col_2) then null else col_2 end as col_2
      ,col_3
from   test_130;

COL_1	COL_2	COL_3
1	1	2
null	null	3
null	null	4
null	2	1
null	null	3
null	null	2
2	1	1
null	null	2
Previous Topic: how to calculate work time or lead time except weekends (merged)
Next Topic: Compare passing values of array with table column
Goto Forum:
  


Current Time: Wed Dec 07 10:26:06 CST 2016

Total time taken to generate the page: 0.07766 seconds