Finding duplcate data in one table column

  • I have a table with column "stud_id".  This field should be unique, but before that can happen, I need to locate and cleanup duplicate data.  Can anyone help me create a select statement to find duplicate stud_id numbers in the column.

    There is also 'null' data in the column.

    Thank you.

  • you don't say what other columns you have in your table...regardless - here's one way to do it (source:ken henderson's "guide to t-sql")...

    create a second table with an identical structure to your "tblStud"...

    then do a create unique index deleteDupes on tblStud2(stud_id, col2, col3..) with ignore_dup_key

    finally a insert tblStud2 select * from tblStud should give you your unique rows.







    **ASCII stupid question, get a stupid ANSI !!!**

  • select stud_id, count(stud_id)

    from table

    group by stud_id

    having count(stud_id) > 1

    will give you a list of stud_ids with more than 1 occurence.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Thank you.  This was a great start.

    Here's what I did.

    select stud_id, count(stud_id)

    from stutable1

    group by stud_id

    having count (stud_id) >1

    results gave me stud_id and number of dups against that stud_id

     

    I woul like to take it one step further.  Would like to see different rows of dup stud_id and also see last_name, first_name.

     

    Any help would be appreciated.

  • select stud_id, lname, fname, count(stud_id)

    from stutable1

    group by stud_id, lname, fname

    having count (stud_id) >1

  • This will only list the records with duplicate stud_ids

    select stud_id, last_name, first_name

    from stutable1

    where stud_id in

     (

      select stud_id

      from stutable1

      group by stud_id

      having count (stud_id) >1

    &nbsp

    order by stud_id

  • This is great, how can I get the results to order by last_name then stud_id?

  • select stud_id, last_name, first_name

    from stutable1

    where stud_id in

    (

    select stud_id

    from stutable1

    group by stud_id

    having count (stud_id) >1

    )

    order by last_name, stud_id

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply