How to build the SQL statement

Ludy

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

Frank Kulash

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".

Frank Kulash

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

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.