Exclude duplicate records

Looking for a script to exclude duplicate records in a table.
I want the result to display only the single records, not the duplicated (account number) records in the table.
Additionally, I want to see if the account number records from another table,  match the single (account numbers) records in the above table.  
I have about 30 fields in the table w/duplicates
I am using SQL Server 2005.

Please help!

mctcAsked:
Who is Participating?
 
sm394Commented:
joining with another table

select *
from(
select acct_no, count(acct_no) as cnt
from table1
group by acct_no
having count(acct_no)=1
) t1 join table2 t2 on t1.acct_no=t2.acct_no
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please clarify if there is 1 (or more) field(s) that could help to "choose" which row to take?
0
 
BalkisBrCommented:
Hi,

In your case you just have to use the "distinct keyword", like this:

SELECT DISTINCT yourColumns FROM yourTable
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Tapan PattanaikSenior EngineerCommented:
hi mctc,
 can u describe more...? can u give small example?
0
 
mctcAuthor Commented:
Select Distinct does not provide the intented results.
The result that I want returned from the query would be like below. I only want the account numbers that are not duplicated 100,103,104.
acct_no
100
102
102
102
103
104
 
0
 
sm394Commented:
select count(acct_no), acct_no
from your table
group by acct_no
having count(acct_no)=1
0
 
Tapan PattanaikSenior EngineerCommented:
According to your question this is the answer and if you create a table having value

acct_no
100
102
102
102
103
104

o/p = 100,103,104

then check this Query

select  acct_no
from account
group by acct_no
having count(acct_no)=1
0
 
Tapan PattanaikSenior EngineerCommented:
I forgot to mention in the  above post that  :  "account " is my table name.
0
 
mctcAuthor Commented:
Thank you for the quick response, it worked great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.