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  |
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 #390142 is a reply to message #390097] |
Thu, 05 March 2009 02:46  |
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
|
|
|
Goto Forum:
Current Time: Sat Feb 15 11:07:33 CST 2025
|