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 entries in a search

Is there a way to find only unique entries for a search. Currently I am
looking for jobs that a run for a give database, but the script will
return multiple job entries for the same job if the database is
reference many times in that job, (once per reference) is there a way
to weed out the duplicates?
Thanks.
-Matt-
<code>
select sj.name
from msdb.dbo.sysjobs sj join msdb.dbo.sysjobsteps sjs
on sj.job_id = sjs.job_id
where sjs.database_name = 'database_name' order by 'name'
</code>Try,
select sj.name
from msdb.dbo.sysjobs sj
where exists (
select *
from msdb.dbo.sysjobsteps sjs
where sjs.database_name = 'database_name' and sjs.job_id = sj.job_id
)
order by 'name'
go
You can also use "DISTINCT" in the original statement.
AMB
"Matthew" wrote:

> Is there a way to find only unique entries for a search. Currently I am
> looking for jobs that a run for a give database, but the script will
> return multiple job entries for the same job if the database is
> reference many times in that job, (once per reference) is there a way
> to weed out the duplicates?
> Thanks.
> -Matt-
>
> <code>
> select sj.name
> from msdb.dbo.sysjobs sj join msdb.dbo.sysjobsteps sjs
> on sj.job_id = sjs.job_id
> where sjs.database_name = 'database_name' order by 'name'
> </code>
>|||Thanks
Works perfectly.

Remote ODBC Connection problem \

Hi

I think i have a very unique issue ...

Running 2 machines
Win2000 / WinXP
Both have Sql / Service packs
Identical ODBC Setup

The issue is as follows :
Both machines CAN access The internet server through ODBC ! (so i dont
think the problem lies here)
I CAN connect to a server on the internet through a local web page on
W2k Box; BUT NOT on the WINXP machine ?
I think there is a IIS Prob maybe .. Checked iUser Security settings on
both and are identical

Any 1 have a suggestion ?

Thanks in advancelighthammer (lighthammer@.webmail.co.za) writes:
> I think i have a very unique issue ...
> Running 2 machines
> Win2000 / WinXP
> Both have Sql / Service packs
> Identical ODBC Setup
> The issue is as follows :
> Both machines CAN access The internet server through ODBC ! (so i dont
> think the problem lies here)
> I CAN connect to a server on the internet through a local web page on
> W2k Box; BUT NOT on the WINXP machine ?
> I think there is a IIS Prob maybe .. Checked iUser Security settings
> on both and are identical

I'm having a hard time to understand what you are connecting to. Is
"the internet server" and "a server on the Internet" the same machine?
Where does IIS comes into the picture? (Warning: I know next to nothing
about IIS.)

What error messages do you get?

Could the Windows firewall have anything to do with it?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp