Showing posts with label duplicate. Show all posts
Showing posts with label duplicate. Show all posts

Monday, March 12, 2012

List out duplicate names only

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

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)