Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Location
    London, England
    Posts
    8

    Unanswered: Er can you incorporate "BREAK ON" in an SQL statement?

    All,

    I know in SQLPlus you can use a BREAK ON command to remove duplicate column values so the data being pulled back is more readable, but that is two seperate statements.

    Before

    SQL> SELECT id, id2
    2 FROM test
    3 ORDER BY id;

    ID ---------- ID2

    1 ---------- 2
    1 ---------- 3
    2 ---------- 2
    3 ---------- 1

    After
    SQL> BREAK ON id
    SQL> SELECT id, id2
    2 FROM test
    3 ORDER BY id;

    ID ---------- ID2

    1 ---------- 2
    **---------- 3
    2 ---------- 2
    3 ---------- 1

    But is there another command that I can use? within one SQL statement to do something similar or the same result?

    As some apps like Oracle Discoverer can do this for you.. .but how do you do this within an SQL statement?!?! within a program?

    Like for example select id, id2
    from test
    order by break on id

    I know it's not correct but still I want something like this !
    or something like that !! HELP !


    hope this makes sense.

  2. #2
    Join Date
    Mar 2005
    Location
    London, England
    Posts
    8
    Don't I've managed to find what I wanted by tweaking the Case and RANK command.
    Can't believe I forgot about that one !

    select case when rank() over (partition by id order by id2) = 1
    then
    id
    else null end as id,
    id2
    from test

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This *might* be another way to do it, or might be not; check on your (I hope) more complicated and live example:
    Code:
    SELECT DECODE(id, lag(id) over (ORDER BY id), NULL,
                                                  id
                 ) i1,
      id2
    FROM TEST
    If you need an ORDER BY clause, try with such one:
    Code:
    ORDER BY NVL( DECODE(id, lag(id) over (ORDER BY id), NULL, id), 
                  lag(id) over (ORDER BY id)
                )

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721

Posting Permissions

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