Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Finding Duplicates

    I am trying to complete an insert from query but the problem is I have duplicates, so I'm getting an error message. So to correct it I am creating a Find Duplicates statement in the Query analyzer but Its not working can someone tell me whats wrong with this statement (by the way I'm in SQL 2000 Server)

    thank you

    SELECT EmployeeGamingLicense [TM#]AS [TM# Field], Count([TM#])AS NumberOfDups
    FROM TERMINATION
    GROUP BY [TM#]
    HAVING Count([TM#])>1;
    GO

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey Desiree...been a while...

    What's this:

    Code:
    EmployeeGamingLicense [TM#]AS [TM# Field],
    Doesn't look like one column...how about the DDL for the TERMINATION table...

    Or how about

    Code:
    SELECT [TM#], Count([TM#]) AS NumberOfDups
    FROM TERMINATION
    GROUP BY [TM#]
    HAVING Count([TM#])>1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Oh sorry

    HI BRETT!!! HOw ya Been???


    Your right totally over looked that, thank you. Works perfect now. Got another question though, what if I wanted to find duplicate records from one table to another. I would like to be able to see if I can find matching records in two tables One is the TERMINATION.tbl the other EmployeeGamingLicense.tbl. Can I make this into a Stored procedure?

    SELECT EmployeeGamingLicense.[TM#], EmployeeGamingLicense.LASTNAME, EmployeeGamingLicense.FIRSTNAME, EmployeeGamingLicense.[SSN#]
    FROM EmployeeGamingLicense LEFT JOIN TERMINATION ON EmployeeGamingLicense.[TM#] = TERMINATION.[TM #]
    WHERE (((TERMINATION.[TM #]) Is Not Null));

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by desireemm
    what if I wanted to find duplicate records from one table to another. I would like to be able to see if I can find matching records in two tables....
    I'd have to ask you what that means....

    For example...in your previous query, you found dups...

    Do you want to see the rows in the License table where you have dups in TERMINATION?

    Do you want to see where you have dups because of a join between the 2?

    I'm not sure....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Nevremind

    I'm good my statement worked I found the duplicates I needed to find. The thing is I'm Inheriting databases and I need to find the errors with them before I create the Gui for the client. Thank you Brett

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    So you have to audit the entire db that has a poorly defined schema....

    good luck

    Any dates or numerics in varchar fields?

    Once you clean up the dups...do you plan to put unique constarints on?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    no

    No the dates are in (datetype: smalledatetime) and the Numbers like SSN# is in (datetype: Int)

  8. #8
    Join Date
    Sep 2003
    Posts
    364
    A little off topic but what do you do with SSN's that begin with 0?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Anything that does not get math applied to it, is not a number in my book....

    You want to constrain it to be numeric...that's fine....otherwise it's char or varchar
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Brett

    is that ok though for them to be Int and smalldatetime, or do you suggest char or nvarchar

  11. #11
    Join Date
    Sep 2003
    Posts
    364
    I agree, we use char(9) in our db's but I've had to fight off developers that want SSN and TIN fields as int datatypes. I hate to have to determine if I need to add leading zeros back to SSN's that are int datatypes or if the SSN is truly corrupt/bad data.

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    I'm glad

    Ok this is defintely one of those learning processes I desperatley need. I love the feed back forces me to analyze the datatypes more deeply especially for future purposes. Ok so you would reccommend char instead.

  13. #13
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    OK thinking out loud

    Ok Interger data consist of negative and positive whole numbers such as -15, 0, 5, and 2,509. Ok so INT base data type is a Number in the range from -2,147,483,648 through 2,147483.647 only requires 4 bytes of storage per value). Ok and char can consist of numberics, and can consist of up to 8 KB

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dates should defenitley stay as dates....why small though?

    Is space an issue?

    Just a rule of thumb.....

    Does math need to applied? No? Then it's char or varchar...

    Now here's another one...

    Are you're dates nullable or not nullable?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by peterlemonjello
    A little off topic but what do you do with SSN's that begin with 0?
    Worse yet, what do you do for the wierdo's among us with letters in their SSN?

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •