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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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
 
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