sql query

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.

 
rnsrAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
possibly this article helps:
http://www.experts-exchange.com/A_3203.html
0
 
Pratima PharandeCommented:
    -- 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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
OP_ZaharinCommented:
- 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
 
PilouteCommented:
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
 
OP_ZaharinCommented:
- you catch em Piloute ;)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sure.... except that this kind of request does not seem to be homework for me ... I might be wrong, though
0
 
rnsrAuthor Commented:
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
 
PilouteCommented:
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
 
rnsrAuthor Commented:
Thanks P
0
 
abbas_najafizadehCommented:
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
 
rnsrAuthor Commented:
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
 
OP_ZaharinCommented:
- 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
 
rnsrAuthor Commented:
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
 
OP_ZaharinCommented:
- the answer is yes for your condition where you need to display all column :)
0
 
rnsrAuthor Commented:
Great this is where i was  confused why use group by clause when I cannot get the desired result from query.

0
 
rnsrAuthor Commented:
Any other suggestions on this topic.
0
 
rnsrAuthor Commented:
help me to get the concept clear.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.