How i can write query return the douplicate record with deffrint IDs ?! [modified]

[AJ Hoge]

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

[Mycroft Holmes]

Do some basic research[^] This is a common problem that has been solved over and over again. The first 2 items look interesting.

Never underestimate the power of human stupidity RAH

[dan sh]

You asked samething a few posts down. You got the reply and as able to do this as well. Now, why are you posting this again?

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

[AJ Hoge]

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 ?

[AJ Hoge]

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

[dan sh]

If you will read Shameel's reply to your earlier post, you should be able to figure it out. Anyways, a general query would be:

select [colNames]
from tableName
group by [colNames]
having count(*) > 1

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

[AJ Hoge]

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 .

[Luc Pattyn]

All you need is a variation on SQL code shown by others. Try this:

SELECT ID, otherfields FROM table
GROUP BY otherfields
HAVING COUNT(*) > 1

i.e. don't use MAX(ID) or TOP(ID) and include all fields ("otherfields") that are relevant for identity checking :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

---

[puri keemti]

Use CTE feature of SQL server 2005..... Example:

;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)

AS

(

SELECT

Product, SaleDate, SalePrice,

Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC)

FROM SalesHistory

)

DELETE FROM SalesCTE

WHERE Ranking > 1

[dan sh]

As Luc mentioned, you don't need to group by ID. You will get the duplicate records then.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

[AJ Hoge]

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

[AJ Hoge]

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

[AJ Hoge]

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. :)

[Blue_Boy]

Hi, This query works for SQL Server2005,but try to convert for Access2007. A)

CREATE TABLE #tmpData
(
id INT IDENTITY(1,1),
custname VARCHAR(MAX)
)

INSERT INTO #tmpData
SELECT m.Customername
FROM mytable1 m
GROUP BY m.Customername,m.phonenumber,m.city,m.street HAVING COUNT(m.Customername)>1

DECLARE @i AS INT
DECLARE @query AS nvarCHAR(MAX)

SET @i=0
SET @query = 'select * from mytable1 where customername in ('

WHILE(@i<(SELECT COUNT(*) FROM #tmpData))
BEGIN
SET @i=@i+1
SET @query=@query+(SELECT char(39)+custname + char(39) FROM #tmpData WHERE id=@i)+','

END
SET @query=SUBSTRING(@query,1,LEN(@query)-1)
SET @query=@query+')'

EXEC( @query)
DROP TABLE #tmpData

I just played around and found too way to work for Access (I tested it on Access 2003 and not for 2007 version,but i think it should work too for Access2007 version) B)

select a.id,a.Customername , a.phonenumber, a.city, a.street
from mytable1 a
inner join
(select Customername , phonenumber, city, street
from mytable1
group by customername,phonenumber,city,street
having count(*) > 1) b
on a.Customername = b.Customername

---

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

modified on Monday, December 7, 2009 9:32 AM

[Niladri_Biswas]

Try this Will work for Sql Server(Any version), MySql,Oracle, MSAccess

select * from myTable where customername in (
select customername
from myTable
group by customername,phonenum,city,street
having(count(customername)>1 and count(phonenum)>1 and count(city)>1 and count(street)>1))

Output:

id customername phonenum city street
19 jon 555 NK st.5
37 jon 555 NK st.5

Hope this helps :)

Niladri Biswas