Hello everyone.... Actually i am working over a problem and to fullfil that i need to do following: 1. Get all the data depending upon some condition from the table. 2. And with each row it should give it's row count. e.g. 1. a b c d 2. e f g h If a b c d and e f g h are columns values. I tried using row_count but all in vain... Can anyone tell me how may i do this... I am just allowed to use query (sql) Urgent help will be greatly appreciated. Thanks..

Please post your query, then we can take a look. Also see this thread: http://www.club-oracle.com/forums/loop-instruction-t3746/

Hi, SQL> select * from alpha; C - A B C D E F E F G H Z H Z G Z 15 rows selected. SQL> desc alpha Name Null? Type ----------------------------------------- -------- ---------------------------- COL VARCHAR2(1) SQL> select count(col), col from alpha group by col order by col; COUNT(COL) C ---------- - 1 A 1 B 1 C 1 D 2 E 2 F 2 G 2 H 3 Z 9 rows selected. SQL>declare 2 a number; 3 a1 number; 4 b varchar2(100); 5 b1 varchar2(10); 6 cursor c is select count(col), col from alpha group by col order by col; 7 begin 8 open c; 9 fetch c into a,b; 10 loop 11 fetch c into a1,b1; 12 exit when c%notfound; 13 if a=a1 then 14 b:=b||' '||b1; 15 else 16 dbms_output.put_line(a||' '||b); 17 a:=a1; 18 b:=b1; 19 end if; 20 end loop; 21 dbms_output.put_line(a||' '||b); 22 close c; 23* end; SQL> / 1 A B C D 2 E F G H 3 Z PL/SQL procedure successfully completed. Regards,...

Thanks for all your help but i am not allowed to use pl/sql :-( Just structured query language.... Here is the query... Code (SQL): SELECT what_here???(i wrote ROW_COUNT but ALL IN vain),tl.Name,tl.Company,tl.STATUS FROM abc tl, def ts WHERE tl.id=ts.id;

Actually i am using reports... And now i've got idea to use Formula Column.... I try to write Code (SQL): FUNCTION CF_1FORMULA0011 RETURN NUMBER IS i NUMBER :=0; BEGIN i:=i+1; RETURN i; END; in Formula Column but it kept giving errors... Then one of my senior told me to use summary column inside the query and use count function at report level.... I really thanks alot to you guys that you helped me alot but actually i realize that i was wrong... Sorry to all of you and thanks as well.... Next time i'll be more specific regarding this...

Thi is one of the several ways of doing it. Code (SQL): SQL> SELECT * FROM alpha; C - A B C D E F E F G H Z C - H Z G Z 15 ROWS selected. SQL> SELECT COUNT(col), col FROM alpha GROUP BY col ORDER BY col; COUNT(COL) C ---------- - 1 A 1 B 1 C 1 D 2 E 2 F 2 G 2 H 3 Z 9 ROWS selected. SQL> SELECT CNT, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL,' ')),' ') str FROM ( 2 SELECT COUNT(COL) CNT, COL, 3 ROW_NUMBER() OVER (PARTITION BY COUNT(COL) ORDER BY COL) CURR, 4 ROW_NUMBER() OVER (PARTITION BY COUNT(COL) ORDER BY COL)-1 PREV 5 FROM SCOTT.ALPHA GROUP BY COL ORDER BY COL) 6 GROUP BY CNT 7 CONNECT BY PREV = PRIOR CURR AND CNT = PRIOR CNT 8 START WITH CURR = 1; CNT STR ---------- ------------------------- 1 A B C D 2 E F G H 3 Z SQL>

Thanks for your reply... First, i've been solved the problem as problem was something else. My wrong description about problem and i am sorry for that Second, This will assign serial numbers to the group but i wanted to do this per record, so here it fails

Please explain the problem in brief so that somebody may help you. Also t give us the table data and proposed output.

I have been told above...... Just before your post... And i have found the solution that i've been described.... Thanks for your support