SQL FIND DUPLICATE VALUES - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL FIND DUPLICATE VALUES

Author

Topic

DaveyB
Starting Member

10 Posts

Posted - 2008-05-28 : 06:45:22

I have written this script: What I want to do now is, with the new JOINED table find and display all the duplicates custID WHERE the price is either 100 OR 200. Can anyone help? I am very new to all this and can't see how to do it. Thanks!

  
  SELECT 
  *
  FROM 
    table_customer T1
  INNER JOIN 
    table_itemsBought T2
  ON
    T2.custID = T1.custID
  WHERE
    price = '100'
    OR price = '200';

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-28 : 06:57:35

[code]SELECT
T2.CustID
FROM
table_customer T1 INNER JOIN table_itemsBought T2
ON
T2.custID = T1.custID
WHERE
price in ('100', '200')
Group by
T2.CustID
having count(*) > 1[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 07:26:25

Are you looking to display the only duplicate records? if yes, you can use ROW_NUMBER() function

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY T1.CustID ORDER BY T1.CustID)AS RowNo,*
FROM 
  table_customer T1 INNER JOIN table_itemsBought T2
ON
  T2.custID = T1.custID
WHERE
  price in ('100', '200'))t
WHERE t.RowNo >1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources