concatenate all rows

[Rajendran]

Hi
This Query is working fine.Thanks a lot for your response.

Thanks
RajendranR

[simply_dba]

Yes, but the statement is more likely to fetch the answer the original poster wanted than using min()

[John Spencer]

Yes, but the statement is more likely to fetch the answer the original poster wanted than using min()

WHERE ROWNUM = 1 means get me one random row from the query. The row returned can and will change for any number of reasons. Bonker demonstrated one way (delete/insert), changing access paths is another (e.g. Oracle decides to use an index instead of a full scan). Another reason would be simply order of insertion.

SQL> CREATE TABLE t (col1 VARCHAR2(1));
 
Table created.
  
SQL> INSERT INTO t VALUES ('e');
 
1 row created.
 
SQL> INSERT INTO t VALUES ('d');
 
1 row created.
 
SQL> INSERT INTO t VALUES ('c');
 
1 row created.
 
SQL> INSERT INTO t VALUES ('b');
 
1 row created.
 
SQL> INSERT INTO t VALUES ('a');
 
1 row created.
 
SQL> COMMIT;
 
Commit complete.

SQL> SELECT REPLACE(col1, ' ')
  2  FROM (SELECT SYS_CONNECT_BY_PATH(col1,' ') col1, level
  3        FROM t
  4        START WITH col1 = (SELECT MIN(col1) FROM t)
  5        CONNECT BY PRIOR col1 < col1
  6        ORDER BY level DESC)
  7        WHERE rownum = 1;
 
REPLACE(COL1,'')
-----------------
abcde
 
SQL> SELECT REPLACE(col1, ' ')
  2  FROM (SELECT SYS_CONNECT_BY_PATH(col1,' ') col1, level
  3        FROM t
  4        START WITH col1 = (SELECT col1 FROM t WHERE rownum = 1)
  5        CONNECT BY PRIOR col1 < col1
  6        ORDER BY level DESC)
  7        WHERE rownum = 1
 
REPLACE(COL1,'')
-----------------
e

So, which one is more likely to be correct?

TTFN
John