Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate rows (Oracle,9i,XP)
Duplicate rows [message #380539] Mon, 12 January 2009 04:16 Go to next message
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

Re: Duplicate rows [message #380541 is a reply to message #380539] Mon, 12 January 2009 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Duplicate rows [message #380545 is a reply to message #380539] Mon, 12 January 2009 04:53 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks,I have solved my problem,actually my query is working fine and the problem is in my table level constraints .


Thanks for the support

Re: Duplicate rows [message #380546 is a reply to message #380539] Mon, 12 January 2009 04:55 Go to previous message
tlananthu
Messages: 18
Registered: April 2007
Location: India
Junior Member

There is no problem with your query.
Previous Topic: insert date and time
Next Topic: SQL 1999 problem
Goto Forum:
  


Current Time: Sun Dec 04 18:41:27 CST 2016

Total time taken to generate the page: 0.04756 seconds