Mombu the Microsoft Forum sponsored links

Go Back   Mombu the Microsoft Forum > Microsoft > SQL SERVER (TECHNET) > Find Duplicate Data
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 25th July 07:39
travisr
External User
 
Posts: 1
Default Find Duplicate Data


Hi ,

Can someone help me to build a query base on :
SELECT Full_Name,FLT_ID, FLT_DT_ID,PNR_ID

Where Full_Name,FLT_ID, FLT_DT_ID is duplicate in the same table

Thank You very much
--
Travis Tan
  Reply With Quote


  sponsored links


2 25th July 07:39
tony sebion
External User
 
Posts: 1
Default Find Duplicate Data


This query worked for me:

SELECT Full_Name,FLT_ID, FLT_DT_ID,PNR_ID
FROM TABLE
GROUP BY Full_Name,FLT_ID, FLT_DT_ID,PNR_ID
HAVING (COUNT(*) > 1)

Good luck,
Tony Sebion

-----Original Message-----
From: Travis [mailto:Travis@discussions.microsoft.com]
Posted At: Wednesday, August 03, 2005 2:31 AM
Posted To: mseq
Conversation: Find Duplicate Data
Subject: Find Duplicate Data


Hi ,

Can someone help me to build a query base on :
SELECT Full_Name,FLT_ID, FLT_DT_ID,PNR_ID

Where Full_Name,FLT_ID, FLT_DT_ID is duplicate in the same table

Thank You very much
--
Travis Tan
  Reply With Quote
3 25th July 07:39
hugo kornelis
External User
 
Posts: 1
Default Find Duplicate Data


SELECT a.Full_Name, a.FLT_ID, a.FLT_FT_ID, a.PNR_ID
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Full_Name = a.Full_Name
AND b.FLT_ID = a.FLT_ID
AND b.FLT_FT_ID = a.FLT_FT_ID
AND b.PNR_ID <> a.PNR_ID

or

SELECT a.Full_Name, a.FLT_ID, a.FLT_FT_ID, a.PNR_ID
FROM MyTable AS a
WHERE EXISTS
(SELECT *
FROM MyTable AS b
WHERE b.Full_Name = a.Full_Name
AND b.FLT_ID = a.FLT_ID
AND b.FLT_FT_ID = a.FLT_FT_ID
AND b.PNR_ID <> a.PNR_ID)

or

SELECT a.Full_Name, a.FLT_ID, a.FLT_FT_ID, a.PNR_ID
FROM MyTable AS a
INNER JOIN (SELECT Full_Name, FLT_ID, FLT_FT_ID
FROM MyTable
GROUP BY Full_Name, FLT_ID, FLT_FT_ID
HAVING COUNT(*) > 1) AS b
ON b.Full_Name = a.Full_Name
AND b.FLT_ID = a.FLT_ID
AND b.FLT_FT_ID = a.FLT_FT_ID

Try them all in your database to see which one gives the best
performance.

Disclaimer: All queries above are untested, since you didn't provide
CREATE TABLE and INSERT statements to test them on.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
  Reply With Quote


  sponsored links


Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666