[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Exclude duplicate records

Posted on 2009-04-15
9
Medium Priority
?
545 Views
Last Modified: 2012-05-06
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!

0
Comment
Question by:mctc
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24151231
can you please clarify if there is 1 (or more) field(s) that could help to "choose" which row to take?
0
 
LVL 3

Expert Comment

by:BalkisBr
ID: 24151247
Hi,

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

SELECT DISTINCT yourColumns FROM yourTable
0
 
LVL 22

Expert Comment

by:Tapan Pattanaik
ID: 24151273
hi mctc,
 can u describe more...? can u give small example?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:mctc
ID: 24151824
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
 
LVL 13

Expert Comment

by:sm394
ID: 24151948
select count(acct_no), acct_no
from your table
group by acct_no
having count(acct_no)=1
0
 
LVL 13

Accepted Solution

by:
sm394 earned 2000 total points
ID: 24152007
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
 
LVL 22

Expert Comment

by:Tapan Pattanaik
ID: 24152064
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
 
LVL 22

Expert Comment

by:Tapan Pattanaik
ID: 24152344
I forgot to mention in the  above post that  :  "account " is my table name.
0
 

Author Closing Comment

by:mctc
ID: 31570619
Thank you for the quick response, it worked great!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question