September 16, 2008 at 6:41 am
I recieved some data where certain fields are supposed to be unique but are not, I need to isolate the records where the same IDs are repeated so that I can get to the bottom of what is going on. How do I perform a selection of just records where a value in a field is not unique?
i.e. something like:
Select * from (table name) where (field name) is not unique
What would be the correct syntax for that?
September 16, 2008 at 6:47 am
Hi there,
This will list all the [Field Names] that are repeated more than once and how many times they are repeated.
[font="Courier New"]
SELECT
[Field Name],
COUNT(*)
FROM [Table Name]
GROUP BY [Field Name]
HAVING COUNT(*) > 1[/font]
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 17, 2008 at 2:17 am
Hi there,
Did this help?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 17, 2008 at 2:52 pm
Yes, thank you very much, that helped a lot.
September 19, 2008 at 12:53 am
What you actually want to do is find those rows in the table that have the duplicate values and then join that back to the table. I usually use a derived table to do this.
Let's say you have a table, Customers, where CustName should be unique and you find that it's not. The way to look at all the data is something like this:
SELECT Customers.*
FROM Customers
INNER JOIN
(SELECT CustName, COUNT(*) AS NameCount
FROM Customers
GROUP BY CustName
HAVING COUNT(*) > 1
) AS X ON
Customers.CustName = X.CustName
ORDER BY Customers.CustName
This will allow you to see everything in the Customers table where the name was a duplicate.
Todd Fifield
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply