Forum Stats

  • 3,839,756 Users
  • 2,262,532 Discussions
  • 7,901,052 Comments

Discussions

How to build the SQL statement

Ludy
Ludy Member Posts: 50
edited Nov 16, 2009 11:31PM in SQL & PL/SQL
Hi,

Below is the table and data, and i had done the solution using a cursor in pl/sql,
can this be accomplished in a SQL statemnt ?




create table TEMP_DISTINCT
(
A_ID NUMBER not null,
B_ID NUMBER not null,
C_VAL VARCHAR2(30)
)


INSERT INTO TEMP_DISTINCT(A_ID,B_ID,C_VAL) VALUES(111,2222,'One')
INSERT INTO TEMP_DISTINCT(A_ID,B_ID,C_VAL) VALUES(111,2222,'Two')
INSERT INTO TEMP_DISTINCT(A_ID,B_ID,C_VAL) VALUES(112,2222,'Three')
INSERT INTO TEMP_DISTINCT(A_ID,B_ID,C_VAL) VALUES(112,2222,'Four')
INSERT INTO TEMP_DISTINCT(A_ID,B_ID,C_VAL) VALUES(112,2221,'Five')
INSERT INTO TEMP_DISTINCT(A_ID,B_ID,C_VAL) VALUES(112,2221,'Six')


declare
vorgid varchar2(30);
cursor cur is
select distinct A_ID, B_ID from TEMP_DISTINCT
order by A_ID,B_ID;
begin
for curs in cur
loop
select C_VAL
into vorgid from TEMP_DISTINCT
where curs.A_ID = A_ID
and curs.B_ID=B_ID
and rownum = 1;

dbms_output.put_line(curs.A_ID|| ' : ' || curs.B_ID || ':' || substr(vorgid,1,22));
end loop;
end;

Output
-------------
111 : 2222:One
112 : 2221:Five
112 : 2222:Three

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,261 Red Diamond
    edited Nov 16, 2009 11:01PM Answer ✓
    Hi,

    When you use ROWNUM directly on a table, or on a view or result set that does not have an ORDER BY clause, the results are indeterminate, that is, there is no guaranteee you will get the same results every time you run the query, even if the table hasn't changed.

    The analytic ROW_NUMBER function can be used in place of ROWNUM. ROW_NUMBER can compute several different sequences (on different partitions of the result set) at the same time, like this:
    WITH    got_rnum    AS
    (
        SELECT  a_id
        ,       b_id
        ,       c_val
        ,       ROW_NUMBER () OVER  (PARTITION BY  a_id, b_id
                                     ORDER BY      ROWID
                                    )   AS rnum
        FROM    temp_distinct
    )
    SELECT  a_id
    ,       b_id
    ,       c_val
    FROM    got_rnum
    WHERE   rnum    = 1
    ;
    When you use ROW_NUMBER, you have to ORDER BY something. In a very small table, like the sample one, the arbitrary results might be coming in ROWID order in your PL/SQL code, so I used "ORDER BY ROWID" above. I could have used a constant, e.g. "ORDER BY 0".

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,261 Red Diamond
    edited Nov 16, 2009 11:01PM Answer ✓
    Hi,

    When you use ROWNUM directly on a table, or on a view or result set that does not have an ORDER BY clause, the results are indeterminate, that is, there is no guaranteee you will get the same results every time you run the query, even if the table hasn't changed.

    The analytic ROW_NUMBER function can be used in place of ROWNUM. ROW_NUMBER can compute several different sequences (on different partitions of the result set) at the same time, like this:
    WITH    got_rnum    AS
    (
        SELECT  a_id
        ,       b_id
        ,       c_val
        ,       ROW_NUMBER () OVER  (PARTITION BY  a_id, b_id
                                     ORDER BY      ROWID
                                    )   AS rnum
        FROM    temp_distinct
    )
    SELECT  a_id
    ,       b_id
    ,       c_val
    FROM    got_rnum
    WHERE   rnum    = 1
    ;
    When you use ROW_NUMBER, you have to ORDER BY something. In a very small table, like the sample one, the arbitrary results might be coming in ROWID order in your PL/SQL code, so I used "ORDER BY ROWID" above. I could have used a constant, e.g. "ORDER BY 0".
  • Ludy
    Ludy Member Posts: 50
    Thanks Frank Kulash,
    your answer helps me in having a better understanding now.
    As i was thinking to use :
    select * from temp_distinct where rowid in (select min(rowid) from temp_distinct group by a_id, b_id)

    but as you have pointed out rowid arbitrary results might be coming.
    Will use the Analytical fn.
This discussion has been closed.