-
Select duplicate data
I'm trying to select the duplicates of the ID field. The script I've written works fine for finding any of the other fields except the ID field. Why does it work for all other fields and not for the ID field? I have even tried to alias the ID field, but to no avail. Any help would be greatly appreciated.
This works:
SELECT Email, COUNT(*) as 'Number Duplicates'
FROM Users
GROUP BY Email
HAVING COUNT(*) > 1
This doesn't:
SELECT ID, COUNT(*) as 'Number of Duplicates'
FROM Users
GROUP BY [ID]
HAVING COUNT(*) > 1
Thanks...
-
Alexander,
Your query SELECT ID, COUNT(*) as 'Number of Duplicates'
FROM Users
GROUP BY [ID]
HAVING COUNT(*) > 1
In ORACLE should be written as
SELECT ID, COUNT(*) as "Number of Duplicates"
FROM Users
GROUP BY ID
HAVING COUNT(*) > 1
-REMOVE the [] around ID column in GROUP BY clause
Now you mention that the first one works-- I don't see how because in ORACLE syntax, the ALIAS should have
"Number of Duplicates" if the alias constitutes more than one word .
NOTE the double quotes.
If the alias is a single word, then you need No quotes around the word.
HTH
Last edited by nandeep; 03-13-2003 at 12:46 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|