remove duplicate rows

i have a table with NO constraints and duplicate rows(e.g one row is inserted four times) now I want to remove the rows in such a way that only one row from the duplicate rows should stay in the table. Is there any query to solve this problem.
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...Here is your solution:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q139444
"enoch jadhav" <enochjadhav@.myway.com> wrote in message
news:ejrKmIblDHA.424@.TK2MSFTNGP10.phx.gbl...
> i have a table with NO constraints and duplicate rows(e.g one row is
inserted four times) now I want to remove the rows in such a way that only
one row from the duplicate rows should stay in the table. Is there any query
to solve this problem.
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

REMOVE DUPLICATE ROWS

Hi everyone.

How can I get the unique row from a table which contains multiple rows
that have exactly the same values.

example:
create table test (
c1 as smallint,
c2 as smallint,
c3 as smallint )

insert into test values (1,2,3)
insert into test values (1,2,3)

i want to remove whichever of the rows but I want to retain a single
row.

TIA

DiegoHi

There are several ways of doing this... You can select distinct rows into a
temporary table (See DISTINCT in books online), clear out your main table
and then re-populate it. If you have an differentiating column then you can
use that to delete rows that are not (say) the minimum value of that
column, or you could stop rows being put in the table in the first place by
having a unique index or primary key over the columns that should be
distinct, or using a not exists clause when inserting the data.

E.g.
..
SELECT DISTINCT *
INTO #SimpleExample
FROM Test

TRUNCATE TABLE TEST

INSERT INTO TEST ( c1, c2, c3 )
SELECT * FROM #SimpleExample

As there is no AS in a CREATE TABLE statement you will have problems with
this DDL, there is also no information regarding PKs etc which would have
been useful. See http://www.aspfaq.com/etiquette.asp?id=5006

John

"Diego Rey" <diegobph@.yahoo.com> wrote in message
news:e09be785.0412042052.52c8c7e5@.posting.google.c om...
> Hi everyone.
> How can I get the unique row from a table which contains multiple rows
> that have exactly the same values.
> example:
> create table test (
> c1 as smallint,
> c2 as smallint,
> c3 as smallint )
> insert into test values (1,2,3)
> insert into test values (1,2,3)
> i want to remove whichever of the rows but I want to retain a single
> row.
> TIA
> Diego|||John Bell (jbellnewsposts@.hotmail.com) writes:
> As there is no AS in a CREATE TABLE statement you will have problems with
> this DDL, there is also no information regarding PKs etc

Obviously, if he has identical rows in his table, there is no primary key.

Which all good tables in relational database is supposed to have, and thus
this explains why this operation is not a trivial one to perform. You are
simply not supposed to wind up in this situation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> Obviously, if he has identical rows in his table, there is no primary
>> key...Which all good tables in relational database is supposed to have,

Is that a "relational heresy" or a "relational orthodoxy" or an "attempt to
appease the ideas of relational theory" ?( ..kidding :-) )

--
Anithsql

remove duplicate row

how do i write a query that will remove duplicate rows (rows with the same
"username" field) and keep the one with lower ID
ID USERNAME
1 john
2 john2
3 john
after executing this query this should become
ID USERNAME
1 john
2 john2
Thanks,
HowardDo:
DELETE FROM tbl
WHERE id NOT IN ( SELECT MIN( t1.id )
FROM tbl t1
WHERE t1.username = tbl.username ) ;
You can also re-write this using derived table construct or using the
EXISTS() clause.
Anith|||Hi Anith,
I'll be doing the 2005 alternatives today. ;-)
WITH Dups AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY username) AS rn
FROM dbo.T1
)
DELETE FROM Dups
WHERE rn > 1;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uAMZ9Rd3FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Do:
> DELETE FROM tbl
> WHERE id NOT IN ( SELECT MIN( t1.id )
> FROM tbl t1
> WHERE t1.username = tbl.username ) ;
> You can also re-write this using derived table construct or using the
> EXISTS() clause.
> --
> Anith
>|||I should add an ORDER BY clause...
WITH Dups AS
(
SELECT *,
ROW_NUMBER()
OVER(PARTITION BY username ORDER BY id) AS rn
FROM dbo.T1
)
DELETE FROM Dups
WHERE rn > 1;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23vqZQ%23e3FHA.1140@.tk2msftngp13.phx.gbl...
> Hi Anith,
> I'll be doing the 2005 alternatives today. ;-)
> WITH Dups AS
> (
> SELECT *,
> ROW_NUMBER() OVER(PARTITION BY username) AS rn
> FROM dbo.T1
> )
> DELETE FROM Dups
> WHERE rn > 1;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> Join us for the SQL Server 2005 launch at the SQL W in Israel!
> [url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uAMZ9Rd3FHA.3976@.TK2MSFTNGP15.phx.gbl...
>

Remove cursor from trigger - multi row updates

Remove cursor from trigger - multi row updates
I need to remove to following cursor for the delete trigger (I didn't
design it) as it is causing too much of a bottle neck on the server.
What would be the best way to impletement this, bearing in mind that it
would have to take in account multi row functionality as the system
very rarely has single row deletes. I have SQL2K Enterprise on
Win2KServer.
CREATE TRIGGER [tri_DelRecs] ON MyTable
FOR DELETE
AS
SET NOCOUNT ON
DECLARE @.MasterID int
DECLARE dc2 CURSOR FOR
SELECT [Master ID] FROM Deleted
OPEN dc2
FETCH NEXT FROM dc2 INTO @.MasterID
WHILE @.@.FETCH_STATUS = 0
BEGIN
delete from [tb1] where [master id] = @.masterid
delete from [tb2] where [master id] = @.masterid
delete from [tb3] where [master id] = @.masterid
delete from [tb4] where [master id] = @.masterid
delete from [tb5] where [master id] = @.masterid
.. .. .. .. .. .. ..
.. .. .. .. .. .. ..
delete from [tbn] where [master id] = @.masterid
FETCH NEXT FROM dc2 INTO @.Masterid
END
CLOSE dc2
DEALLOCATE dc2
Would I able to accomplish this using a series of JOINs on master id?
Thanks
qhdelete from [tb1] where [master id] in (Select [Master ID] from Deleted)
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Scott" <quackhandle1975@.yahoo.co.uk> schrieb im Newsbeitrag
news:1113569793.664500.141850@.g14g2000cwa.googlegroups.com...
> Remove cursor from trigger - multi row updates
> I need to remove to following cursor for the delete trigger (I didn't
> design it) as it is causing too much of a bottle neck on the server.
> What would be the best way to impletement this, bearing in mind that it
> would have to take in account multi row functionality as the system
> very rarely has single row deletes. I have SQL2K Enterprise on
> Win2KServer.
>
> CREATE TRIGGER [tri_DelRecs] ON MyTable
> FOR DELETE
> AS
> SET NOCOUNT ON
> DECLARE @.MasterID int
> DECLARE dc2 CURSOR FOR
> SELECT [Master ID] FROM Deleted
> OPEN dc2
> FETCH NEXT FROM dc2 INTO @.MasterID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> delete from [tb1] where [master id] = @.masterid
> delete from [tb2] where [master id] = @.masterid
> delete from [tb3] where [master id] = @.masterid
> delete from [tb4] where [master id] = @.masterid
> delete from [tb5] where [master id] = @.masterid
> .. .. .. .. .. .. ..
> .. .. .. .. .. .. ..
> delete from [tbn] where [master id] = @.masterid
>
> FETCH NEXT FROM dc2 INTO @.Masterid
> END
> CLOSE dc2
> DEALLOCATE dc2
>
> Would I able to accomplish this using a series of JOINs on master id?
>
> Thanks
> qh
>|||delete [tb1]
where exists (select * from deleted as d where d.[master id] = tb1.[master
id])
...
AMB
"Scott" wrote:

> Remove cursor from trigger - multi row updates
> I need to remove to following cursor for the delete trigger (I didn't
> design it) as it is causing too much of a bottle neck on the server.
> What would be the best way to impletement this, bearing in mind that it
> would have to take in account multi row functionality as the system
> very rarely has single row deletes. I have SQL2K Enterprise on
> Win2KServer.
>
> CREATE TRIGGER [tri_DelRecs] ON MyTable
> FOR DELETE
> AS
> SET NOCOUNT ON
> DECLARE @.MasterID int
> DECLARE dc2 CURSOR FOR
> SELECT [Master ID] FROM Deleted
> OPEN dc2
> FETCH NEXT FROM dc2 INTO @.MasterID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> delete from [tb1] where [master id] = @.masterid
> delete from [tb2] where [master id] = @.masterid
> delete from [tb3] where [master id] = @.masterid
> delete from [tb4] where [master id] = @.masterid
> delete from [tb5] where [master id] = @.masterid
> ... .. .. .. .. .. ..
> ... .. .. .. .. .. ..
> delete from [tbn] where [master id] = @.masterid
>
> FETCH NEXT FROM dc2 INTO @.Masterid
> END
> CLOSE dc2
> DEALLOCATE dc2
>
> Would I able to accomplish this using a series of JOINs on master id?
>
> Thanks
> qh
>|||Have you considered using ON DELETE CASCADE instead of a trigger?
Never use cursors in triggers, for one thing the set-based code is
usually much simpler:
CREATE TRIGGER [tri_DelRecs] ON mytable FOR DELETE
AS
DELETE FROM tb1
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE [master id] = tb1.[master id])
DELETE FROM tb2
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE [master id] = tb2.[master id])
... etc
David Portas
SQL Server MVP
--|||Change your DDL on the referencing tables:
CREATE TABLE subord_n
(...
master_id INTEGER NOT NULL
REFERENCES Master (master_id)
ON DELETE CASCADE,
.);
Using DRI actions also gives the optimizer information about the
relationships, so all your code gets better.|||Thanks for all your replies guys, much appreciated!
I have used been testing and I think I have what I'm after using the
following trigger, although I will try the ON DELETE CASCADE.
Cheers
qh|||Doh!
CREATE TRIGGER [tri_MyTrigger] ON [dbo].[tbl1]
FOR INSERT, UPDATE, DELETE
AS
IF @.@.ROWCOUNT = 0
BEGIN
PRINT 'There are no records to delete!'
RETURN
END
IF NOT EXISTS(SELECT * FROM DELETED)
--sql block for insert
INSERT INTO tbl2([ID], [Name], [Team])
SELECT [ID], [name], [Team] FROM Inserted
ELSE
IF NOT EXISTS(SELECT * FROM INSERTED)
--sql block for delete
DELETE FROM tbl2
WHERE tbl2.[ID] IN (SELECT [ID] FROM Deleted)
ELSE
--sql block for update
UPDATE tbl2
SET [Team] = Inserted.[Team], [Name] = Inserted.[Name]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
qh

Remove "blank" space?

I have a report that contains a table with two rows. Each of these rows
contains a table with a header row, detail row and footer row. When the
report loads, these inner tables expand to 5-20 rows of data. All well and
good. However, sometimes the top inner table has no data to render. But when
the report renders, this space is blank (the height of the row of the outer
table). So there's a big gap of whitespace at the top of the report.
Is there any way to have this space shrink to nothing (or nearly nothing)?
Or at least very small? I can't seem to find any way to make this happen.
Thanks,
Ripleywrite an expression on the visibilty expression
"Ripley" <ripley@.ihatespam.com> wrote in message
news:epyIiUg0GHA.4228@.TK2MSFTNGP06.phx.gbl...
>I have a report that contains a table with two rows. Each of these rows
>contains a table with a header row, detail row and footer row. When the
>report loads, these inner tables expand to 5-20 rows of data. All well and
>good. However, sometimes the top inner table has no data to render. But
>when the report renders, this space is blank (the height of the row of the
>outer table). So there's a big gap of whitespace at the top of the report.
> Is there any way to have this space shrink to nothing (or nearly nothing)?
> Or at least very small? I can't seem to find any way to make this happen.
> Thanks,
> Ripley
>sql