duplicate values

Question

How to find out the duplicate values for more than one column in table

Is it possible to find out the dup.values from single query or

any sproc will be more helpful

Answer 1

How about this...

 

 

Code Snippet

SELECT COLUMN1,COUNT(COLUMN1),COLUMN2,COUNT(COLUMN2) FROM table_name

GROUP BY COLUMN1,COLUMN2

HAVING COUNT(COLUMN1)>1 AND COUNT(COLUMN2)>1

 

 

Answer 2

And if you want to find out entire duplicate row then

 

Code Snippet

select max(id_column),sname from table1

group by sname

having count(*)>1

 

 

Answer 3

Hi Mangal,

 

Thank u so much

 

Answer 4

Thanks, here is wat i am looking.

Here is the example, see 3 and 4 are having same values in 4 records and 5 th one is different. I need to raise a flag if the 5th one is different from  4 others. It should be any one field.

Last Name	First Name	Decision Date	DecisionNumber	SSN
Smith	John	20120106	0	XXX-XX-1234
Smith	John	20120106	0	XXX-XX-1234
Smith	John	20120106	1	XXX-XX-1234
Smith	John	20120106	1	XXX-XX-4567

Answer 5

Here is the best example.

Suppose I have a SQ L table like this:
+-----+-----+-----+-----+-----+
|  A  |  B  |  C  |  D  |  E  |
+=====+=====+=====+=====+=====+
|  1  |  2  |  3  | foo | bar |  
+-----+-----+-----+-----+-----+
|  1  |  2  |  3  | foo | bar |  
+-----+-----+-----+-----+-----+
|  1  |  2  |  4  | foo |  bar|
+-----+-----+-----+-----+-----+
|  1  |  4  |  3  | foo | bar |  
+-----+-----+-----+-----+-----+
|  1  |  2  |  5  | foo | bar |  
+-----+-----+-----+-----+-----+
|  1  |  3  |  3  | foo | bar |  
+-----+-----+-----+-----+-----+
+-----+-----+-----+-----+-----+
|  1  |  3  |  3  | tom | bar |  
+-----+-----+-----+-----+-----+
+-----+-----+-----+-----+-----+
|  1  |  3  |  3  | foo | bar |  
+-----+-----+-----+-----+-----+
 

Now, I want to the below result
+-----+-----+-----+-----+-----+
|  A  |  B  |  C  |  D  |  E  |
+-----+-----+-----+-----+-----+
|  1  |  2  |  4  | foo |  bar|
+-----+-----+-----+-----+-----+
|  1  |  4  |  5  | foo | bar |  
+-----+-----+-----+-----+-----+
|  1  |  3  |  3  | tom | bar |  
+-----+-----+-----+-----+-----+