Query asked by Satyam Interviewer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Query asked by Satyam Interviewer

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-10-28 : 08:00:20

Anil Singh writes "I went for interview at satyam.

He asked me to write a query to delete the duplicate records
from the table as :

ID Name

1 A

2 B

1 A

2 B

2 B

3 C

3 C

4 D

I have to write a single query to delete the duplicate records, keeping single copy of each record.

Delete from table1.......
"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-28 : 08:44:07

read this:

http://www.sqlteam.com/item.asp?ItemID=3331

you could also use union...
select * from
(select * from table1
union
select * from table1) t1
insert that into a temp table. delete the records from previous table and insert non duplicates from temp table into an old table.

i don't think you can delete the duplicates with one delete... at least i haven't seen it yet.

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-28 : 13:43:58

this loops and deletes 1 duplicate at a time...
( not the fastest way though )

set nocount on
set rowcount 0

create table #t(ID int, Name varchar)
insert #t
select 1,'A' union all
select 2,'B' union all
select 1,'A' union all
select 2,'B' union all
select 2,'B' union all
select 3,'C' union all
select 3,'C' union all
select 4,'D'

set rowcount 1; select '' -- The select is just to get @@rowcount = 1
while @@rowcount > 0
	delete #t
	from #t
	join( select ID,Name from #t group by ID,Name having count(*) > 1 ) dups
	on #t.ID = dups.ID and #t.Name = dups.Name

set rowcount 0
select * from #t

drop table #t

rockmoose

dsdeming

479 Posts

Posted - 2004-10-29 : 11:12:38

Here's another method that I've used.

SET NOCOUNT ON

CREATE TABLE #temp( ID int, Name char( 1 ))
INSERT INTO #temp SELECT 1, 'A'
INSERT INTO #temp SELECT 2, 'B'
INSERT INTO #temp SELECT 1, 'A'
INSERT INTO #temp SELECT 2, 'B'
INSERT INTO #temp SELECT 2, 'B'
INSERT INTO #temp SELECT 3, 'C'
INSERT INTO #temp SELECT 3, 'C'
INSERT INTO #temp SELECT 4, 'D'

ALTER TABLE #temp ADD RowNumber int IDENTITY( 1, 1 )
GO

DELETE FROM #temp
WHERE RowNumber NOT IN( SELECT RowNumber = MIN( RowNumber ) FROM #temp GROUP BY ID, Name )

ALTER TABLE #temp DROP COLUMN RowNumber
GO

SELECT * FROM #temp

DROP TABLE #temp

Dennis

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-29 : 12:29:30

It's hard to say how badly this will perform, but it satisfies the 'single query' requirement (at least it's a single statement, and the interviewer probably won't know what it does.)

master..xp_execresultset 'select ''set rowcount ''+cast(count(1)-1 as varchar)+''; delete from table1 where id = ''+cast(id as varchar) from table1 group by id, name having count(1) > 1',N'mydb'

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 12:37:11

Nice Kselvia

I tried to "conform" to the 1 query requirement,
but I didn't go as far as You did *LOL*
But doesn't your query purge the table a bit too much ?
( for the record - didn't try it )

rockmoose

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-29 : 12:38:08

It shouldn't because it sets rowcount rows - 1 where rows > 1.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 12:59:34

You are right Kselvia, didn't read it well enough.

nice nice

give that to the interviewer

, and I am not sure you will get hired,
unless it's SqlTeam that is hiring...

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 13:30:26

"You're hired because you know how to use SQL Team" ... We need the Snitz Interview-click-through-commission module!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources