how to get distinct records [message #18930] |
Thu, 21 February 2002 09:05 |
ksr
Messages: 112 Registered: January 2002
|
Senior Member |
|
|
Hi,
i want distinct records with the select statement inside the plsql.
I have something like this.
declare
cursor c1 is select col1,col2,col3 from table1 where not exists(select col1 from table2 where table1.col2 = table2.col1);
begin
for c1 in cur1 loop
insert into.....
end loop;
end;
data
----
col1 col2 col3
1 one 123
1 two 234
2 three 456
2 four 567
3 five 958
4 six 746
I want my cursor to fetch one of the records whose col1 valus is same.
i mean i want..the resultset returned by the select statement to be
1 one 123
2 three 456
3 five 958
4 six 746
How can i do it...
|
|
|
Re: how to get distinct records [message #18931 is a reply to message #18930] |
Thu, 21 February 2002 11:05 |
Jon
Messages: 483 Registered: May 2001
|
Senior Member |
|
|
This should do it...
declare
cursor c1 is select col1,col2,col3 from tab1 t1 where not exists(select col1 from table2 where t1.col2 = table2.col2) and rowid = (select min(rowid) from tab1 t2 where t1.col1 = t2.col1);
begin
for c1rec in c1 loop
insert...
end loop;
end;
|
|
|
Re: how to get distinct records [message #19154 is a reply to message #18930] |
Sun, 03 March 2002 22:59 |
jeya
Messages: 18 Registered: January 2001
|
Junior Member |
|
|
the query can be written like this
select col1,col2,col3 from table1 where rowid in(
select min(rowid) from table1 group by column)
column refers to the coulmn which is duplicate
|
|
|