How to identify rows having the same values in 4 columns ?
-
Hi all !
I need to find which rows have the same values in 4 columns in 1 table ?
What is the SQL script which will do it ?
THANKS !
-
SELECT
...
FROM
tblName
WHERE
Col1 = Col2 AND
Col1 = Col3 AND
Col1 = Col4
Should do it.
-
ok thank you, i was looking for something more complicated
In my case it's :
SELECT
...
FROM
tblName
WHERE
Col1 = Col1 AND
Col2 = Col2 AND
Col3 = Col3 AND
Col4 = Col4
-
Actually I thought Antares response made good sense as answer to your question
Probably you are trying to find duplicate records.
( Consider putting a Primary Key or Unique Constraint on the 4 columns if applicable ).
Sample script:
select tblName.* from
tblName join(
select col1, col2, col3, col4 from tblName
group by col1, col2, col3, col4
having count(*) > 1) as dupTable
on tblName.col1 = dupTable.col1
and tblName.col2 = dupTable.col2
and tblName.col3 = dupTable.col3
and tblName.col4 = dupTable.col4
/rockmoose
You must unlearn what You have learnt -
The better way
select count (*),col1,col2,col3,col4
from tbl
group by col1,col2,col3,col4
having count (*) >1
-
Better if You only want to list the distinct values of col1, col2, col3, col4.
A join or exists clause is still necessary if You want to list all the rows of the table containing the duplicates on col1, col2, col3, col4.
/rockmoose
You must unlearn what You have learnt -
So if you do the following:
insert into t1 (a,b,c,d)
select a,b,c,d from t2
where (t1.a <> t2.a) AND (t1.b <> t2.b) AND (t1.c <> t2.c) and (t1.d <> t2.d) AND (t2.e = "john")
would this insert all the ones where a,b,c,d fields in t1 are NOT equal to a,b,c,d fields in t2???
tarique
-
All the t2.e = "john" records in t2 with a,b,c,d fields not equal to a,b,c,d fields in t1
/rockmoose
You must unlearn what You have learnt
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply