|
View previous topic :: View next topic
|
| Author |
Message |
rupali_shah
Warnings : 1
New User
Joined: 14 Mar 2009
Posts: 6
Location: Hyderabad
|
|
|
|
WE HAVE A TABLE WITH X COLUMNS AND THE QUERY IS TO FIND THE
NUMBER OF ROWS WHICH HAVE DUPLICATE VALUES IN
THREE COLUMNS.
FOR EXAMPLE IN TABLE TAB1
WE HAVE FOUR COLUMNS
| Code: |
COL1 COL2 COL3 COL4
01 342 232 2112
02 342 232 2342
03 342 232 2342
04 342 441 2342 |
If we Want the number of rows having duplicate values in set of COL2, COL3 AND COL4, the result of the rows query should be like this:
| Code: |
COL1 , COL2, COL3, COL4
02 342 232 2342
03 342 232 2342 |
Edited: Please use BBcode when You post some code/error, that's rather readable, Thanks... Anuj
Warning: Turn Off Caps |
|
| Back to top |
|
 |
Succor
New User
Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)
|
|
|
|
Rupali,
Try the following Query and let us know if suits your requirement.
| Code: |
SELECT DISTINCT X.A,X.B,X.C,X.D
FROM EMP X ,(SELECT A,B,C,D FROM EMP ) AS Y
WHERE X.B = Y.B
AND X.C = Y.C
AND X.D = Y.D
AND X.A <> Y.A ; |
Replace EMP with your table name and A,B,C,D with your respective column names.
WTF |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Hi,
If I understood correctly, try something like:
| Code: |
select col2,col3, col4, count(*)
from table
group by col2,col3, col4
having count(*) > 1 |
|
|
| Back to top |
|
|
senthilnathanj
New User
Joined: 31 Jul 2007
Posts: 47
Location: chennai
|
|
|
|
Hi rupali,
Try the following query and let us know...
| Code: |
WITH TEMP(COL1,COL2) AS (
SELECT COL1,COL2 FROM TAB1 GROUP BY COL1,COL2 HAVING COUNT(*) > 2)
SELECT A.COL1,A.COL2 FROM TAB1 A WHERE A.COL1 IN (SELECT T.COL1 FROM TEMP T) AND A.COL2 IN (SELECT T.COL2 FROM TEMP T);
|
u can get the extract output.. |
|
| Back to top |
|
|
gkanswa
New User
.jpg)
Joined: 09 Feb 2009
Posts: 5
Location: Pune
|
|
|
|
Hi Rupali,
You can try this one -
| Code: |
Select * from TAB1 A
where 2 <= (select count (distinct Col2) from TAB1
where COL2= A.COL2 and
COL3 = A.COL3 and
COL4= A.COL4); |
"Code"d
I don't have mainframe access, so couldn't test it.
Please do correct me if I am wrong.
Girish |
|
| Back to top |
|
|
|
|
