how to bring back a list of duplicates from a column

TimG

I have a column that has 75 values, 50 are unique/25 are duplicates. I
need to be able to bring back a list of the duplicates, listing all
rows even if more than two have the same value. I need to be able to
do this using t-sql.

Thanks,

Tim

Jul 20 '05 #1

Subscribe Reply

3454

David Portas

SELECT S1.*
FROM Sometable AS S1
JOIN
(SELECT col
FROM Sometable
GROUP BY col
HAVING COUNT(*)>1) AS S2
ON S1.col = S2.col

--
David Portas
------------
Please reply only to the newsgroup
--

Jul 20 '05 #2

Anyone,
I'm doing something similar with duplicates, but I'm not a SQL guru. Could
you explain some of your syntax? Specifically, what does "FROM .. AS" and
"JOIN" and "ON S1.col=S2.col" do? Are S1 and S2 names of temporary result
lists you're creating, or have something to do with the table name?

I have records where multiple people have the same address and I only want
to send one letter to the address for everyone that lives there. I'm doing
it in Access.

Thanks, Amanda
"David Portas" <RE************ *************** *@acm.org> wrote in message
news:tf******** ************@gi ganews.com...

SELECT S1.*
FROM Sometable AS S1
JOIN
(SELECT col
FROM Sometable
GROUP BY col
HAVING COUNT(*)>1) AS S2
ON S1.col = S2.col

--
David Portas
------------
Please reply only to the newsgroup
--

Jul 20 '05 #3

David Portas

Depending on the version, Access supports different features and different
syntax so refer to your documentation or post a question to an Access
newsgroup. Access 2000 and earlier versions won't run this query.

In Access you do have a Find Duplicates Wizard and if you're not familiar
with SQL you may want to use that. Click New in the Query pane and select
the wizard from the menu.

--
David Portas
------------
Please reply only to the newsgroup
--

Jul 20 '05 #4

Thanks, but I'm not having a problem getting a list of duplicates, just
getting a table that only has the first of each duplicate.... -Amanda
"David Portas" <RE************ *************** *@acm.org> wrote in message
news:wr******** ************@gi ganews.com...

Depending on the version, Access supports different features and different
syntax so refer to your documentation or post a question to an Access
newsgroup. Access 2000 and earlier versions won't run this query.

In Access you do have a Find Duplicates Wizard and if you're not familiar
with SQL you may want to use that. Click New in the Query pane and select
the wizard from the menu.

--
David Portas
------------
Please reply only to the newsgroup
--

Jul 20 '05 #5

Pink Panther

Hi Amanda,

Your still posting in the wrong Newsgroup - it doesn't worry me,
however I can only speak for myself.

http://groups.google.com.au/groups?h...ases.ms-access
(watch the wrap; is one access newsgroup)

Anyways - define 'first'.
Also when asking about SQL it is benefical to give people some idea of
your table structure.

Do you have a primary key defined on your table or some other unique
index? A timestamp or Autonumber so you can identify the 'first'
record?

If you just want a results set of records with duplicates removed use
the DISTINCT predicate.

In Access goto the query builder and View > Properties

and set the Unique Values Property to Yes.

However dulicates break the relational model and are to be frowned
upon. I would recommend defining a primary key for your table.

Regards,

Peter

"AH" <am*****@southw ind.org> wrote in message news:<vv******* *****@corp.supe rnews.com>...

Thanks, but I'm not having a problem getting a list of duplicates, just
getting a table that only has the first of each duplicate.... -Amanda
"David Portas" <RE************ *************** *@acm.org> wrote in message
news:wr******** ************@gi ganews.com...
Depending on the version, Access supports different features and different
syntax so refer to your documentation or post a question to an Access
newsgroup. Access 2000 and earlier versions won't run this query.

In Access you do have a Find Duplicates Wizard and if you're not familiar
with SQL you may want to use that. Click New in the Query pane and select
the wizard from the menu.

--
David Portas
------------
Please reply only to the newsgroup
--

Jul 20 '05 #6

Similar topics

7809

identify duplicates in an array and number of times duplicated

by: Michelle | last post by:

hi, i have created an array from recordset containing user names eg. (davidp, davidp, evenf, patricka, rebeccah) which i have sorted in alphabetical order, but i need to identify duplicates in this array and the number of times it has been duplicated. can someone help?

ASP / Active Server Pages

4682

filtering and flagging a mailing list for 1 record per household

by: Mark V. | last post by:

Here's what I have and I'm stumped. I have a table that has several thousand names and addresses. I only want to send to one address in a household. So what I would like to do is create a new column that runs a macro or whatever it takes to flag records so they are housholded. Some records have a Father and son at the same address, so I would only want to generate a count in my query that gives me one record for that household, a head of...

Microsoft Access / VBA

1984

error when selecting data from the dropdown list

by: Doslil | last post by:

I have a form which has a subform.In the main form I have only one field (this is a drop down list and has a query attached to it) which selects empno,Name from the EmployeeInformation table. The sub form stores the time sheet data for each employee.When I am able to do the timesheet data entry for the first record.But when I select the second employee from the dropdown list and click on the subform for entering the timesheet data, it...

Microsoft Access / VBA

5395

Code : DoCmd.OpenQuery - and bring result back to form front

by: Tyson | last post by:

I have got this little piece of code that fires on exit of a text box. It run's my query perfectly based of my form input. But I don't know how to bring result in my query back to a text box on my form, and shut down query so I can keep using my form again. Thoughts? I am getting a little freaked, I am sure this must be easy? form is; frm_RR_datacapture query is;qprGB_Piece_SizefromTextBox textbox...

Microsoft Access / VBA

19473

Combo Box "Not In List" VBA code to Add item to table

by: Bob Alston | last post by:

I have a drop down combo box that gives the user to enter an item not in the list by adding it to the list. The list is a table. It works fine on Access2003 but fails on Access2002/XP. ON XP, it seems to work, shows the message that the entry has been added, then I get an Access error saying the item is not in the list. Here is my code: Private Sub Combo10_NotInList(NewData As String, Response As Integer) intAnswer = MsgBox("The...

Microsoft Access / VBA

5766

Code Review (Skip List)

by: Robin Cole | last post by:

I'd like a code review if anyone has the time. The code implements a basic skip list library for generic use. I use the following header for debug macros: /* public.h - Public declarations and macros */ #ifndef PUBLIC #define PUBLIC

C / C++

2193

Deleting all but one row of a list of non-uniques

by: Zak McGregor | last post by:

Hi all I have a table, for simplicity's sake containing one field, called unid. for example, select unid, oid from table gives me something like this: unid | oid ---------+---------

PostgreSQL Database

1826

Limiting a Bound ComboBox List Items

by: gleadams | last post by:

I have databound a Windows form ComboBox control to a DataSource and DataMember and it is properly showing the data as I navigate through the records. My question concerns the choices in the pull-down list itself. The Field is Priority and the only available choices should be "High", "Low", "Do Not Call". These are the only choices I would like to see in drop-down, but because it is bound to the column it is showing multiple instances...

.NET Framework

4760

nee help with linked list to delete duplicates

by: moon24 | last post by:

Hi im working with linked list and i have to implement a function that deletes the duplicates of a number. for example if given 2 7 1 7 12 7 then the result should be 2 7 1 12 here is what I have: #include <iostream> using namespace std; class NumberList {

C / C++

8685

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8612

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

9032

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

7743

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6532

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5869

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4625

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3053

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2342

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP