Results 1 to 5 of 5

Thread: Query help

  1. #1
    Join Date
    Oct 2003
    Posts
    26

    Unanswered: Query help

    I would appeciate a little help with a query.

    I have two tables, T1 and T2.

    SQL> select * from t1;

    ID RMKS
    ---------- --------------------
    1 TEST1
    2 TEST2
    3 TEST3
    4 TEST4
    5 TEST5
    6 TEST6

    6 rows selected.

    SQL> select * from t2;

    ITEM TEXT
    ---------- --------------------
    1 RESULTS2
    2 RESULTS1
    4 RESULTS1
    1 RESULTS1
    2 RESULTS5
    3 RESULTS1
    4 RESULTS2
    1 RESULTS3
    1 RESULTS5
    2 RESULTS2
    4 RESULTS3

    11 rows selected.


    Right now, my query results display a one for one relationship like:

    SQL> select a.rmks, b.text from t1 a, t2 b where b.item = a.id order by a.rmks, b.text;

    RMKS TEXT
    -------------------- --------------------
    TEST1 RESULTS2
    TEST1 RESULTS1
    TEST1 RESULTS3
    TEST1 RESULTS5
    TEST1 RESULTS4
    TEST1 RESULTS2
    TEST2 RESULTS1
    TEST2 RESULTS2
    TEST2 RESULTS3
    TEST2 RESULTS4
    TEST2 RESULTS5

    What I would like to see is TEST1 with all of the results, TEST2 with all of the results, etc:

    RMKS TEXT
    -------------------- --------------------
    TEST1 RESULTS1
    RESULTS2
    RESULTS3
    RESULTS4
    RESULTS5

    TEST2 RESULTS1
    RESULTS2
    RESULTS3
    RESULTS4
    ...

    I've been to the 'Tahiti' web site and 'Ask Tom' but most of the examples (GROUPING, DECODE, etc) are selections from a single table and I keep getting errors. I feel that I am close but I've already spent way too much time on this before asking for assistance.

    Thanks in advance,

    John

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    this might do the trick

    select case when RMKS = lag(RMKS ,1) over (order by RMKS, b.text) then null else RMKS end x,
    text
    from t1 a, t2 b where b.item = a.id order by a.rmks, b.text

    Alan

  3. #3
    Join Date
    Oct 2003
    Posts
    26
    Alan,

    Thanks... it worked great!

    John

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    in sql*plus you can just issue a BREAK ON command
    PHP Code:
      1  select component_sidattribute_sid
      2
    from TA_COMP_ATTRIBUTE where component_sid 134

    SQL
    > /

    COMPONENT_SID ATTRIBUTE_SID
    ------------- -------------
              
    134             2
              134            35
              134             2
              134            35

    Elapsed
    00:00:00.00
    SQL
    > break on COMPONENT_SID
    SQL
    > /

    COMPONENT_SID ATTRIBUTE_SID
    ------------- -------------
              
    134             2
                             35
                              2
                             35 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Oct 2003
    Posts
    26

    Thumbs up

    Duck,

    That worked too... thanks!

    Have a great weekend...

    John

Posting Permissions

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