We help IT Professionals succeed at work.

sql query

Medium Priority
493 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.

 
Comment
Watch Question

CERTIFIED EXPERT

Commented:
    -- 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
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Top Expert 2011

Commented:
- 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

Commented:
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
Top Expert 2011

Commented:
- you catch em Piloute ;)
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
sure.... except that this kind of request does not seem to be homework for me ... I might be wrong, though

Author

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.

Commented:
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

Author

Commented:
Thanks P
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

Author

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.
Top Expert 2011

Commented:
- 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.

Author

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.
Top Expert 2011

Commented:
- the answer is yes for your condition where you need to display all column :)

Author

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

Author

Commented:
Any other suggestions on this topic.
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
help me to get the concept clear.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.