May 1, 2003 at 5:27 am
i HAVE A TABLE WITH NO UNIQUE KEY AND HAS DUPLICATE RECORDS IN. i CAN GET THE DISTINCT RECORDS OUT BY 'SELECT DISTINCT * FROM TABLENAME'
bUT HOW DO YOU GET OUT THE 'NOT' DISTINCT RECORDS
CAN ANYONE HELP
May 1, 2003 at 6:10 am
Try this:
select a.col1
from table1 a
where 1 = (select count(*) from table1 b
where a.col1 = b.col1)
This uses a self join to bring back rows where there is only 1 occurrence.
If you want duplicates, then change the equals to a less than i.e. where 1 is less than the number of rows.
Jeremy
May 1, 2003 at 6:31 am
I have tried this but it needs to be on the whole record. Each column may have numberous records that are the same but the whole record should be unique but are not. I need to find the records that are not.
May 1, 2003 at 8:04 am
Is there an ID on each row?
If so, have you tried:
SELECT *
FROM mytable
WHERE myid NOT IN (SELECT DISTINCT * FROM mytable)
-SQLBill
May 1, 2003 at 12:26 pm
Try This:
Select <columns to check>
From <table name>
Group By <columns to check>
Having count(*) > 1
Eric
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply