INTERVIEW QUESTIONS DATABASE SQL DETAILS

Question: I have a table with duplicate names in it. Write me a query which returns only duplicate rows with number of times they are repeated.

Answer: SELECT COL1 FROM TAB1
WHERE COL1 IN
(SELECT MAX(COL1) FROM TAB1
GROUP BY COL1
HAVING COUNT(COL1) > 1 )

SELECT COL1 FROM TAB1
WHERE COL1 IN
(SELECT MAX(COL1) FROM TAB1
GROUP BY COL1
HAVING COUNT(COL1) > 1 )

Source: CoolInterview.com

select col, count(col) from tab group by col having count(col)>1 Source: CoolInterview.com

SELECT NAME,COUNT(NAME) FROM TABLE1 GROUP BY NAME
HAVING COUNT(NAME) > 1 Source: CoolInterview.com

u may also try this..
SELECT COUNT(*),NAME FROM TABLE1 GROUP BY NAME HAVING COUNT(*)>1
Source: CoolInterview.com

select name from customer
having count(name)>1
group by name
Source: CoolInterview.com

Find Duplicate Row
-------------------
SELECT NAME,COUNT(NAME) FROM TABLE1 GROUP BY NAME
HAVING COUNT(NAME) > 1

Remove Duplicate Row (Copy & paste and run)
----------------------------------
declare @TBL table(EmpName varchar(10))

insert into @TBL values('Manish')
insert into @TBL values('Manish')
insert into @TBL values('Milan')
insert into @TBL values('Milan')
insert into @TBL values('Mukesh')
insert into @TBL values('Manav')
insert into @TBL values('Madhav')

select * from @TBL -- Before Delete

declare @TBL1 table(ID int identity(1,1),EmpName varchar(10))

insert into @TBL1(EmpName)
select EmpName from @TBL group by EmpName having count(EmpName) > 1

declare @iLoop int,@Cnt int,@EmpName varchar(10)
select @Cnt = count(*) from @TBL1
set @iLoop = 1
while (@iLoop <= @Cnt)
begin
select @EmpName = EmpName from @TBL1 where ID = @iLoop
delete top(1) from @TBL where EmpName = @EmpName
set @iLoop = @iLoop + 1
end

select * from @TBL -- After Delete


Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question