Dear puri keemti, i do not use SQL server , i use sql in access 2007.and i dont need to delet the record that douplicated , i need to display it as it is ,with there different IDS . if it is not clear please see question and my replies. could you help me ? thanks for you brother. :)

Dear d@nish , see my replay to LUC. Thanks

Hi Luc ,you are right, I Wrote that what you say , but i got error that " you try to excute a query that does not include the specified expression 'ID' as part of an aggregate function" Thanks

you are right and i know that ! but if i write this :

select [ID,colNames,Phone,...]from tableNamegroup by [colNames]having count(*) > 1

i will have 0 douplicate record !! , why ? because IDs for each douplicted records are different !! and if use all columns without IDs ,i will got douplicted record without IDs. please see the example in my question , i don't need to ignore any IDs. could you please help me to find way? Thanks .

Dear d@nish , Here i have different case , becuse i have two douplicate record with different ID , and when i use max() or top() function i will be ignor one of the ID ,and this couse problem for me to link to other tables. so i need to display all the two records with there IDs. ould you please help me to do that ?! Thanks

Dear Mycroft , i search befor and after post my question. but i did not find the solustion . could you help me please ?! according my qusetion , my problem is i have tow douplicte record with deffrint ID , when i use Max(ID) or top(ID) functions i will ignor of the IDs ,becuse ID is deffirnt. and i wont to display it as it is with out ignor any IDs . how i can do it ?

Hi ,ALL If i have this Customer table :

ID | CustomerName | PhoneNum | City |Street
19 jon 555 NK st.5
21 Mark 236 TK KA.st
37 jon 555 NK st.5
45 AJ 800 LA WS.17

We note that Customer Name "Jon" added in this table tow time with deffrint IDs . In my real database , i have Thousands of this case. :(( How i can write query return the douplicate record with there IDs ? :confused:, like :

ID | CustomerName | PhoneNum | City |Street
19 jon 555 NK st.5
37 jon 555 NK st.5

modified on Monday, December 7, 2009 4:18 AM

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

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 ?

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**

Thanks Guys ,

Hi All , I'm beganer in SQL ,and I'm going to install aplication in the coustomer PC .My boss ask me to install Microsoft SQL Server 2005 Client version in the customer pc. my qustion is , what is the "Microsoft SQL Server 2005 Client version"? is it the sql native client or not? form where can i install it ? and how? is it free? Many thanks,

hii ,sorry what do you mean by sure-fire signal ? :^) yes i agree with you that the data is look bad , but i work on a huge data that which is extracted by the Customers them self so i can not edit any thing. :( many thanks

:thumbsup:Ohhh...Yes it is work !! many thanks

hii , i have question and i wont ask this question in form of the example. if we have table named CUSTOMER: CUSTOMER TABLE: CustomerID : Integer (Not a primary Key) CustomerName: TEXT

And This is the table's data:
CustomerId | CustomerName
12 | Jone
25 | Mark
25 | Marc
10 | AJ

we note that CustomerId number 25 has deffrint spiling of the CustomerName for the same Customer! i need to return the unique CustomerId with the first CustomerName. i write this querey :

select DISTINCT CustomerID , CustomerName
From Customer

but it got me the four records !! the 25 is dublicated twies becuse the spiling of the name are deffrint for the same Customer how to write querey return the unique CustomerId with the first CustomerName? Many Thanks