Hi all,
I am new this this site. May I know how do I do a search for duplicate items
in a column of a table only. eg If John appears twice and Jim once, in the
Name column of Student table, the Select statement should show me 2 times (
John ) ?
ThanksSee Itzik Ben-Gan's example
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Eric_Singapore" <Eric_Singapore.24lr9q@.mail.codecomments.com> wrote in
message news:Eric_Singapore.24lr9q@.mail.codecomments.com...
> Hi all,
> I am new this this site. May I know how do I do a search for duplicate
> items in a column of a table only. eg If John appears twice and Jim
> once, in the Name column of Student table, the Select statement should
> show me 2 times ( John ) ?
>
> Thanks
>
> --
> Eric_Singapore
> ---
> Posted via http://www.codecomments.com
> ---
>
Showing posts with label duplicate. Show all posts
Showing posts with label duplicate. Show all posts
Monday, March 12, 2012
Monday, February 20, 2012
list all duplicate names (query help)
we have a table with duplicate name
1. how to list all duplicate customer names(occurring more than once), including frequency of occurrence.
2. find out how many customers have more than 2 account
Thanksselect customer, [number of accounts]=count(*)
from customer_account_table
group by customer
having count(*) > 1|||try this query
select cust_name , count(cust_name) from customer_table group by cust_name order by count(cust_name) desc|||Originally posted by ms_sql_dba
select customer, [number of accounts]=count(*)
from customer_account_table
group by customer
having count(*) > 1
Above is answer to question #1. Below is answer to question#2.
select count(*) from (
select customer, [number of accounts]=count(*)
from customer_account_table
group by customer
having count(*) > 1) as q|||actually, asnwer #1 lacks one line to become both #1 and #2:
select customer, [number of accounts]=count(*)
from customer_account_table
group by customer
having count(*) > 1
compute count(customer)
1. how to list all duplicate customer names(occurring more than once), including frequency of occurrence.
2. find out how many customers have more than 2 account
Thanksselect customer, [number of accounts]=count(*)
from customer_account_table
group by customer
having count(*) > 1|||try this query
select cust_name , count(cust_name) from customer_table group by cust_name order by count(cust_name) desc|||Originally posted by ms_sql_dba
select customer, [number of accounts]=count(*)
from customer_account_table
group by customer
having count(*) > 1
Above is answer to question #1. Below is answer to question#2.
select count(*) from (
select customer, [number of accounts]=count(*)
from customer_account_table
group by customer
having count(*) > 1) as q|||actually, asnwer #1 lacks one line to become both #1 and #2:
select customer, [number of accounts]=count(*)
from customer_account_table
group by customer
having count(*) > 1
compute count(customer)
Subscribe to:
Posts (Atom)