OraFAQ Forum: SQL & PL/SQL » Display duplicate record (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Display duplicate record (merged)

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

Display duplicate record (merged) [message #288955]

Wed, 19 December 2007 02:33

ntc2000
Messages: 25
Registered: September 2005

Junior Member

Hi,

I have a case where i need to query out multiple duplicated records from a table. (Transno and refno(reference no) is the primary key for table_a).

SQL> select refno,hdbatchid, count(transno) as counter from table_a group by refno,hdbatchid order by refno;

REFNO HDBATCHID COUNTER
-------------- ---------- ----------
EFT04100700276 NR267 51 (means 51 records)
EFT19090700250 2007T0333 379 (means 379 records)
EFT19090700251 2007T0333 345 (means 345 records)
EFT19090700254 2007T0332 616 (means 616 records)
EFT25090700260 T0002 3 (means 3 records)
EFT25090700281 T0002 1 (means 1 records)

6 rows selected.

How can i create a query where the output will be shown as below?? It's only shown out the duplicate hdbatchid records.

REFNO HDBATCHID COUNTER
-------------- ---------- ----------
EFT19090700250 2007T0333 379 (means 379 records)
EFT19090700251 2007T0333 345 (means 345 records)
EFT25090700260 T0002 3 (means 3 records)
EFT25090700281 T0002 1 (means 1 records)

6 rows selected.

thank for help.

Report message to a moderator

Re: Display duplicate record [message #288958 is a reply to message #288955]

Wed, 19 December 2007 02:44

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

Senior Member
Account Moderator

Add a new level of count (in anaylytic form) and display only those with count > 1.

Regards
Michel

Report message to a moderator

Re: Display duplicate record [message #288962 is a reply to message #288958]

Wed, 19 December 2007 02:53

ntc2000
Messages: 25
Registered: September 2005

Junior Member

can u show me how to do it? thank.

Report message to a moderator

Re: Display duplicate record [message #288964 is a reply to message #288962]

Wed, 19 December 2007 02:55

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

That U person can possibly, but I don't think he is a member of this forum

Report message to a moderator

Re: Display duplicate record [message #288968 is a reply to message #288964]

Wed, 19 December 2007 03:01

ntc2000
Messages: 25
Registered: September 2005

Junior Member

ThomasG wrote on Wed, 19 December 2007 02:55

That U person can possibly, but I don't think he is a member of this forum

wat u mean bro???

Report message to a moderator

Re: Display duplicate record [message #288972 is a reply to message #288968]

Wed, 19 December 2007 03:15

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

Senior Member
Account Moderator

What is "wat"?
What is "u"?
What is "bro"?

Post a test case and I show you.
But before Rrad and follow OraFAQ Forum Guide, especially "How to format your post?" and "IM speak" sections.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Also post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

how to get only duplicate records from a table by using groupby [message #289001 is a reply to message #288955]

Wed, 19 December 2007 04:41

msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR

Member

hello,

my sample query is like this:

select refno,hdbatchid, count(eftno) as counter from eftag group by refno,hdbatchid order by refno;

REFNO  HDBATCHID COUNTER
------ -------- ---------- 
DE343  NR267       51 (means 51 records)
DE250  2007T0333    1 (means 1 records)
DE271  2007T0333  345 (means 345 records)
DE284  2007T0332  616 (means 616 records)
DE288  T0002        3 (means 3 records)
DE299  T34334       1 (means 1 record) 

what i want is like below

DE243  NR267       51 (means 51 records)
DE271  2007T0333  345 (means 345 records)
DE284  2007T0332  616 (means 616 records)
DE288  T0002        3 (means 3 records)

Here i need to exclude the counter(1 record) because i have only one record(no duplicate record).

thanQ in advance,
Ajay.

Report message to a moderator

Re: how to get only duplicate records from a table by using groupby [message #289010 is a reply to message #289001]

Wed, 19 December 2007 04:54

Frank
Messages: 7901
Registered: March 2000

Senior Member

Use the HAVING keyword for that

Report message to a moderator

Re: how to get only duplicate records from a table by using groupby [message #289012 is a reply to message #289001]

Wed, 19 December 2007 04:56

msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR

Member

hi Frank,

First of all thank's a lot for quick reply. Can give me a little example query..

thanQ

Report message to a moderator

Re: how to get only duplicate records from a table by using groupby [message #289015 is a reply to message #289012]

Wed, 19 December 2007 05:01

Frank
Messages: 7901
Registered: March 2000

Senior Member

What did you find when you looked up HAVING and GROUP BY in the manual?

Report message to a moderator

Re: how to get only duplicate records from a table by using groupby [message #289016 is a reply to message #289015]

Wed, 19 December 2007 05:03

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

similar thread.but ,different users????

http://www.orafaq.com/forum/?t=rview&goto=288955#msg_288955

Report message to a moderator

Re: Display duplicate record [message #289017 is a reply to message #288955]

Wed, 19 December 2007 05:03

kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India

Senior Member

Quote:

Date Registered: Tue, September 20, 2005
Message Count: 21 Messages(s) (0.03 average messages per day)
Show all messages by ntc2000
Real Name: ntc2000

Hmmm....

Report message to a moderator

Re: how to get only duplicate records from a table by using groupby [message #289018 is a reply to message #289001]

Wed, 19 December 2007 05:06

kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India

Senior Member

http://www.orafaq.com/forum/m/287345/115564/#msg_287345

Report message to a moderator

Re: Display duplicate record [message #289019 is a reply to message #288955]

Wed, 19 December 2007 05:06

kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India

Senior Member

http://www.orafaq.com/forum/m/287345/115564/#msg_287345

Report message to a moderator

Re: how to get only duplicate records from a table by using groupby [message #289022 is a reply to message #289018]

Wed, 19 December 2007 05:11

Frank
Messages: 7901
Registered: March 2000

Senior Member

No, this is not the same. This one simply wants to filter out counts equal to zero. The other one is more complicated

Although... they do share the same IP

[Updated on: Wed, 19 December 2007 05:11]

Report message to a moderator

Re: how to get only duplicate records from a table by using groupby [message #289036 is a reply to message #289001]

Wed, 19 December 2007 05:53

gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar

Member

try it...

select refno,hdbatchid, count(eftno) as counter 
from eftag 
having count(eftno)>1 
group by refno,hdbatchid 
order by refno;

Regards
Sunil Gaurav

[Updated on: Wed, 19 December 2007 05:59] by Moderator

Report message to a moderator

Re: how to get only duplicate records from a table by using groupby [message #289041 is a reply to message #289022]

Wed, 19 December 2007 06:01

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

Senior Member
Account Moderator

Now they have to explain which one is the correct question.

Regadrs
Michel

Report message to a moderator

Re: how to get only duplicate records from a table by using groupby [message #289348 is a reply to message #289016]

Thu, 20 December 2007 20:48

ntc2000
Messages: 25
Registered: September 2005

Junior Member

thank for all moderator reply. AJAY is my colleague who I asked him the same question. He post on here as well.

SQL> select refno,hdbatchid, count(transno) as counter from table_a group by refno,hdbatchid order by refno;


REFNO HDBATCHID COUNTER
-------------- ---------- ----------
EFT04100700276 NR267 51 (means 51 records)
EFT19090700250 2007T0333 379 (means 379 records)
EFT19090700251 2007T0333 345 (means 345 records)
EFT19090700254 2007T0332 616 (means 616 records)
EFT25090700260 T0002 3 (means 3 records)
EFT25090700281 T0002 1 (means 1 records)

6 rows selected.

I want to query out the output as below.

REFNO HDBATCHID COUNTER
-------------- ---------- ----------
EFT19090700250 2007T0333 379 (means 379 records)
EFT19090700251 2007T0333 345 (means 345 records)
EFT25090700260 T0002 3 (means 3 records)
EFT25090700281 T0002 1 (means 1 records)

I want to query the duplicate HDBATCHID which have difference refno. Based on above query, I have group up the transno and it's has 379 records for hdbatchid=EFT19090700250 and 345 records hdbatchid=EFT19090700251.

And by using the above group by query, how can i make it so that it's will group the refno again based on previous group by result and display the result i want.

Thank for reply.

Report message to a moderator

Re: how to get only duplicate records from a table by using groupby [message #289349 is a reply to message #289348]

Thu, 20 December 2007 20:52

ntc2000
Messages: 25
Registered: September 2005

Junior Member

Quote:

And by using the above group by query, how can i make it so that it's will group the refno again based on previous group by result and display the result i want.

sorry for mistake, suppose is..

And by using the above group by query, how can i make it so that it's will group the hdbatchid again based on previous group by result and count the number of refno(which refno=2) and display the result i want.

Report message to a moderator

Re: how to get only duplicate records from a table by using groupby [message #289350 is a reply to message #289349]

Thu, 20 December 2007 21:30

ntc2000
Messages: 25
Registered: September 2005

Junior Member

one of my colleague found the query.


SQL> select refno,hdbatchid, sum(transno) as counter,count(refno) from (select refno,hdbatchid,count(transno)as transnocnt from table_a group by refno,hdbatchid) test where hdbatchid in (select hdbatchid from (select refno,hdbatchid,count(transno) as transnocnt from table_a group by refno,hdbatchid)test2 group by hdbatchid having count(hdbatchid)>1)group by refno,hdbatchid order by refno;

REFNO          HDBATCHID     COUNTER COUNT(REFNO)
-------------- ---------- ---------- ------------
EFT19090700250 2007T0333         379            1
EFT19090700251 2007T0333         345            1
EFT25090700260 T0002               3            1
EFT25090700281 T0002               1            1

Report message to a moderator

Re: how to get only duplicate records from a table by using groupby [message #289352 is a reply to message #289350]

Thu, 20 December 2007 21:33

ntc2000
Messages: 25
Registered: September 2005

Junior Member


select refno,hdbatchid, sum(transno) as counter,count(refno) from
(select refno,hdbatchid,count(transno)as transnocnt from table_a
group by refno,hdbatchid) test where hdbatchid in (select hdbatchid
from (select refno,hdbatchid,count(transno) as transnocnt from
table_a group by refno,hdbatchid)test2 group by hdbatchid having
count(hdbatchid)>1)group by refno,hdbatchid order by refno

Report message to a moderator

Re: Display duplicate record (merged) [message #289360 is a reply to message #288955]

Thu, 20 December 2007 22:43

kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India

Senior Member

Did you get the solution right?

Kiran.

Report message to a moderator

Re: how to get only duplicate records from a table by using groupby [message #289362 is a reply to message #289348]

Thu, 20 December 2007 23:26

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

Senior Member
Account Moderator

Edit your message to a correct formatting.
Align your columns
Keep your lines in 80 characters.
Use "Preview" button to verify.

Regards
Michel

[Updated on: Thu, 20 December 2007 23:27]

Report message to a moderator

Previous Topic:	Logic of rewriting a Query!!!
Next Topic:	How to CAST a String

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 08:33:49 CST 2024