I have a table with no keys (temp table) which looks like this :
As you see, there is some duplicate entries in it. I would like to get a list of all the rows that have the same col1 and col2 BUT different col3 value. The result should return col1=001 col2=A but NOT col1=001 col2=B. I tried a lot of queries with EXISTS, HAVING, etc... but nothing seems to work.
select distinct test.col1, test.col2, test.col3
(select col1, col2, count(1) as colcount
group by col1, col2
having count(1) > 1) a on test.col1 = a.col1 and test.col2 = a.col2
i also have same problem with just a change that i want to delete the rows which have different value for the column 3 in the above query .
for ex. i want to delete the
i have a data record in V_TABLE table in which values are like this
ID V_TYPE STATUS
2124 H1 APPR
2124 H1 VISA
23573 H1 VISA
23573 H1 APPR
23869 H1 APPR
23869 H1 VISA
5458 L1 VISA
5458 L1 APPR
16673 L1 VISA
16673 L1 APPR
10074 H1 APPR
10093 L1 VISA
10108 L1 VISA
now i want to delete those emplid(complte rows) who have STATUS = 'APPR' only when they have STATUS = 'APPR' as well for same V_TYPE
for ex. 2124 emplID has both 'APPR' n 'VISA' status for H1 V_TYPE so only 'APPR' row must be deleted.
i have applied this code but it is deleting complete table .
DELETE FROM V_TABLE WHERE EXISTS
(SELECT * FROM V_TABLE A where
(SELECT ID FROM V_TABLE B WHERE B.V_STATUS = 'APPR'
AND B.ID = A.EMPLID
AND B.V_TYPE = A.V_TYPE
SELECT ID FROM V_TABLE C WHERE V_STATUS = 'VISA'
AND C.ID = A.ID
AND C.V_TYPE = A.V_TYPE)
AND A.VISA_STATUS = 'APPR'
and A.ID = V_TABLE.ID)
Last edited by Pat Phelan; 01-02-13 at 10:52.
Reason: Trivial formatting changes for readability