[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql query

Posted on 2011-04-27
18
Medium Priority
?
456 Views
Last Modified: 2012-05-11
let the record be -
  id     name
   1     a
   1    a
   2    aaa
   7   ss
  7    ss
   9  ffff
  10 ffff

need help to write query to --
     -- show duplicate record  
     --  do not show duplicate record
    --  show only single record even for duplicate record

The query should work on any of the databases.
Plz do not use rowid,in clause , traversing through loop and checking the record  or other reserved word which are not used in other databases.

   performance and large number record should be considered.

 
0
Comment
Question by:rnsr
  • 7
  • 4
  • 3
  • +3
18 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35481166
    -- show duplicate record  
select id ,name from tablename
group by id,name
having count(*) >1

     --  do not show duplicate record

 select id ,name from tablename
group by id,name
having count(*) =1

    --  show only single record even for duplicate record
select distinct id , name from tablename
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35481175
first could be:
select t.* from yourtable t join ( select id, name from yourtable group by id, name having count(*) > 1 ) x on x.id = t.id and x.name = t.name

Open in new window


second:
select t.* from yourtable t left join ( select id, name from yourtable group by id, name having count(*) > 1 ) x on x.id = t.id and x.name = t.name where x.id is null

Open in new window


third:
select id, name from yourtable group by id

Open in new window

0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35481184
- additional to what have been posted

- duplicate record on id only
SELECT id, count(*) FROM tblname GROUP BY id HAVING count(*) > 1

- duplicate record on name only
SELECT name, count(*) FROM tblname GROUP BY name HAVING count(*) > 1

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 7

Expert Comment

by:Piloute
ID: 35481547
Hi guys,

Nobody relized this seems to be a homework assignment ? We are not supposed to solve homework problems but help the author solve it himself !

:|

P
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35481561
- you catch em Piloute ;)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35481682
sure.... except that this kind of request does not seem to be homework for me ... I might be wrong, though
0
 

Author Comment

by:rnsr
ID: 35506814
This is certainly not w home work but its a question of how the query work with 1 gb or more database . The record can be in different ways.
   If i take it as 10 or 50 record then its a home work type. But this is not so here.
    let the tow 2 rows null then? OR NAME BE NULL THEN?
A similar thinking is required to solve this problem.

  Think for a higher level and then write anything. MR. Piloute.
0
 
LVL 7

Expert Comment

by:Piloute
ID: 35510801
Your first post still looks as a homework assignment to me... Nevermind. If you say it is not homework, it is not. You have already got answers. Try not to be so 'theoretic' next time or just tell us it is not homework...

It won't change anything for me if it was, but please understand we are here to help you analyze and solve problems. Solving a hwrk assignment is helping you cheating. That's all...

Will let the others complete their answers for the extra information you just gave...

Cheers,
P
0
 

Author Comment

by:rnsr
ID: 35512381
Thanks P
0
 
LVL 2

Expert Comment

by:abbas_najafizadeh
ID: 35715325
for more performance you must create data copy into a table like this:

create table tab1 (id number,name char(20),duplicate_count number);

insert into tab1
(id,name,duplicate_count)
select id,name,count(*) from  yourtable group by id,name

then you can select every thing you need:
 -- show duplicate record  
select id,name from tab1
where duplicate_count>1;

--  do not show duplicate record
select id,name from tab1
where duplicate_count=1;

--  show only single record even for duplicate record
select id,name from tab1;

be success
0
 

Author Comment

by:rnsr
ID: 35717391
If there are 50 columns in a record then i had to group by all 50 columns to see the entire record?  i want to see the entire record not jjust one or two columns.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35717417
- you need to be specific to which column that you consider to be duplicate? if you means there are certain rows that have all 50 columns the same, then the answer is yes you need group all 50 columns.
- you cannot display all columns and check for duplicate record at the same time if not all column is duplicated.
- most of the experts here shared various way of querying the duplicate record, you can choose to use which is best and work for you.
0
 

Author Comment

by:rnsr
ID: 35717438
It means the best way to deal with duplicate record is by pl/sql coding. create cursor and check the record for duplicate. in this way we can get the desired result in any format for duulicate record.

Am i right.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35717446
- the answer is yes for your condition where you need to display all column :)
0
 

Author Comment

by:rnsr
ID: 35717454
Great this is where i was  confused why use group by clause when I cannot get the desired result from query.

0
 

Author Comment

by:rnsr
ID: 35717937
Any other suggestions on this topic.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 35719024
possibly this article helps:
http://www.experts-exchange.com/A_3203.html
0
 

Author Closing Comment

by:rnsr
ID: 35790745
help me to get the concept clear.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question