Home » SQL & PL/SQL » SQL & PL/SQL » Display duplicate record (merged)
Display duplicate record (merged) [message #288955] Wed, 19 December 2007 02:33 Go to next message
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.
Re: Display duplicate record [message #288958 is a reply to message #288955] Wed, 19 December 2007 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add a new level of count (in anaylytic form) and display only those with count > 1.

Regards
Michel
Re: Display duplicate record [message #288962 is a reply to message #288958] Wed, 19 December 2007 02:53 Go to previous messageGo to next message
ntc2000
Messages: 25
Registered: September 2005
Junior Member
can u show me how to do it? thank.
Re: Display duplicate record [message #288964 is a reply to message #288962] Wed, 19 December 2007 02:55 Go to previous messageGo to next message
ThomasG
Messages: 3189
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
Re: Display duplicate record [message #288968 is a reply to message #288964] Wed, 19 December 2007 03:01 Go to previous messageGo to next message
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???
Re: Display duplicate record [message #288972 is a reply to message #288968] Wed, 19 December 2007 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use the HAVING keyword for that
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What did you find when you looked up HAVING and GROUP BY in the manual?
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 Go to previous messageGo to next message
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
Re: Display duplicate record [message #289017 is a reply to message #288955] Wed, 19 December 2007 05:03 Go to previous messageGo to next message
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....
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 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

http://www.orafaq.com/forum/m/287345/115564/#msg_287345
Re: Display duplicate record [message #289019 is a reply to message #288955] Wed, 19 December 2007 05:06 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

http://www.orafaq.com/forum/m/287345/115564/#msg_287345
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now they have to explain which one is the correct question.

Regadrs
Michel
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
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

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 Go to previous messageGo to next message
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

Re: Display duplicate record (merged) [message #289360 is a reply to message #288955] Thu, 20 December 2007 22:43 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Did you get the solution right?

Kiran.
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 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Wed Dec 07 18:58:09 CST 2016

Total time taken to generate the page: 0.08627 seconds