concatenate all rows - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: concatenate all rows

  1. #11
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    select min(col1) from t1
    This does not guarantee the first row.How about this?
    select col1 from t1 where rownum=1
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  2. #12
    Join Date
    May 2005
    Posts
    31
    Originally posted by simply_dba This does not guarantee the first row.How about this? select col1 from t1 where rownum=1
    In database there is no concept of first row, last row etc... If you are inserting records with timestamp or with sequence only then you can get the first row inserted or last row inserted.

    In 8k blocksize database try this

    create table test (a char(2000), b char(2000), c char(2000), d char(2000));

    insert into test values ('1','b','c','d');
    insert into test values ('2','b','c','d');
    insert into test values ('3','b','c','d');

    select a from test where rownum=1; -- what is value of a shown by this query?

    delete from test where a='1';

    commit;

    insert into test values ('4','b','c','d');

    select a from test where rownum=1; -- what is value of a now shown by this query?
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  3. #13
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Yes, but the statement is more likely to fetch the answer the original poster wanted than using min()
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  4. #14
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    Quote Originally Posted by simply_dba
    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.

    Code:
    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

  5. #15
    Join Date
    Sep 2007
    Posts
    2

    Smile How can I rollup multiple rows of data into one row

    I have the following table and data:

    CREATE TABLE t1 (col1 VARCHAR2(1), col2 VARCHAR2(25));
    INSERT INTO t1 (col1,col2) VALUES ('1','TEST1');
    INSERT INTO t1 (col1,col2) VALUES ('2','TEST1');
    INSERT INTO t1 (col1,col2) VALUES ('5','TEST2');
    INSERT INTO t1 (col1,col2) VALUES ('9','TEST2');
    INSERT INTO t1 (col1,col2) VALUES ('1','TEST3');
    INSERT INTO t1 (col1,col2) VALUES ('1','TEST4');
    INSERT INTO t1 (col1,col2) VALUES ('5','TEST5');


    I would like to get the following results from a single query:

    co1 col2
    TEST1 1,2
    TEST2 5,9
    TEST3 1
    TEST4 1
    TEST5 5


    If anyone knows how to do this using connect by i would appreciate your help. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width