how to return the douplictate records across only some column using SQL Query ? please help me ! :

AJ Hoge

Hi All , If have table contains of seven columns or more, and i need to return the douplictate records across only three column.how i can writ this using SQL query ? for example i have this table :

Clmn1 | Clmn2 | Clmn3 | Clmn4 .......|Clmn7
AJ 35 25 0 19
BQ 10 12 1 8
KS 35 25 3 19

i need to return all records that have duplicat value in this three columns only "Clmn2" , "Clmn3" and "Colmn7" . like this:

Clmn1 | Clmn2 | colmn3 | clmn4 .......|colmn7
AJ 35 25 0 ** 19**

Thanks,

KS 35 25 3 ** 19**

Lost User

Hint: Use GROUP BY clause with HAVING COUNT(*) > 0

Blue_Boy

Hi,I hope this will help you

SELECT
(SELECT top 1 m2.Clmn1 FROM mytable m2 WHERE m2.Clmn2= m.Clmn2) Clmn1 ,--by this subquery you get first value of clmn1 column
clmn2,clmn3,clmn7
FROM mytable m
GROUP BY m.clmn2,m.clmn3,m.clm7
HAVING
COUNT (col2) > 0 AND
COUNT (col3) > 0 AND
COUNT (col7) > 0

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

AJ Hoge

you know , Your Are Great !! Many thanks, could you please allow me to ask you a question : i wrote this:

SELECT

and it is work , but are there better than this way ? becuse when i put Column1 column in the nested select clause with out to be in group by cluase , i got error that show " the coulmn1 is not in the agregation function " so why this is happen? . instade of that i use join to get Column1 , was what i did right ?

AJ Hoge

yeeees !! Blue_Boy , realy realy i appreciate your reply. you are great and helpfull. yor answer is very celever . Many Thanks

Blue_Boy

You are always welcome bro :)

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com