Results 1 to 2 of 2

Thread: Select duplicate data

  1. #1
    Join Date
    Mar 2003
    Posts
    1

    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...

  2. #2
    Join Date
    Mar 2003
    Location
    CA,USA
    Posts
    18
    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
  •