OraFAQ Forum: SQL & PL/SQL » Duplicate rows

Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate rows (Oracle,9i,XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Duplicate rows [message #380539]

Mon, 12 January 2009 04:16

gozuhair
Messages: 206
Registered: January 2008

Senior Member

Dear All

I am using the below query for displaying duplicate rows but the system is not displaying any rows in response to the following query.

select a.pcode
from lts_tran a
where a.rowid>
(
select min(b.rowid)
from lts_tran b
where a.pcode=b.pcode
and a.sl_prod_cd=b.sl_prod_cd)

my table structure is

SQL> desc lts_tran
 Name                            Null?    Type
 ------------------------------- -------- ----
 PCODE                                    VARCHAR2(10)
 HR                                       VARCHAR2(5)
 SL_PROD_CD                               VARCHAR2(2)

My table data is like this

PCODE      HR     sl_prod_cd
99ZZZVZ0   17.5  10
99ZZZVZ0   17.5  10
99ZZZVZ0   17.5  10
99ZZZVZ0   18    6
99ZZZWZ0   15.5  1
99ZZZWZ0   19.5  10

PCODE      HR    SL_prod_cd

---------- ----- --
99UPBTC0   1.2   1
99UPBTC0   2.2   10
99UPBTR0   1.5   1
99UPBTR0   2.5   10
99UPBTR0   2.5   10

If you see first 3 records and last 2 records than you can find duplication,how can i select only those records thats contains duplication.

Please help

Regards

Zuhair

Report message to a moderator

Re: Duplicate rows [message #380541 is a reply to message #380539]

Mon, 12 January 2009 04:27

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And what do you get?

Post a Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste your session.

Regards
Michel

Report message to a moderator