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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How to show duplicate result

Author  Topic 

AsimKhaliq
Yak Posting Veteran

94 Posts
Posted - 2004-03-15 : 18:19:11
Hi
I have a table and i have some duplicates record based on address and state in thattable, So what I want to do is to show duplicate enteries only .So my result would me like this

Name|Address|city|state

John|1234 keel|abc|FL
Mike|1234 keel|abc|FL

Andy|52 westSt|abc|CA
Lucy|52 westSt|abc|CA

Can anyone help me

Thnx in adv
Asim

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-03-15 : 18:21:45
SELECT Column1, Column2, COUNT(*)
FROM Table1
GROUP BY Column1, Column2
HAVING COUNT(*) > 1

Change Column1, Column2 to the columns that show where the duplicates are.

Tara
Go to Top of Page

AsimKhaliq
Yak Posting Veteran

94 Posts
Posted - 2004-03-15 : 18:28:05
Thnx
if u see my example I have 4 colums to show but duplicates are based on only two columns. So if one record have have address and state with others than show all the fields and in group by i can show them until unless i have to match with all, which i dont want to


Thnx
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-03-15 : 18:31:35
I don't understand what you mean. Using your sample data, what would the expected result set be?

Tara
Go to Top of Page

AsimKhaliq
Yak Posting Veteran

94 Posts
Posted - 2004-03-15 : 18:37:52
Thnx

Ok
what i want is to show all record of one address and state in a row like this, here john and mike have same address and state and same for andy and lucy. NOw what ur solution can do is just get the 2 colums but not all columns.Plz feel free to ask me if u still have any confusion.

Name|Address|city|state

John|1234 keel|abc|FL
Mike|1234 keel|abc|FL

Andy|52 westSt|abc|CA
Lucy|52 westSt|abc|CA
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-03-15 : 18:46:01
I don't see how your expected result set is different than your sample data. But maybe you mean this:


SELECT t1.Name, t2.Address, t2.City, t2.State
FROM Table1 t1
INNER JOIN (
		SELECT Address, City, State
		FROM Table1
		GROUP BY Address, City, State
		HAVING COUNT(*) > 1
	   ) AS t2
ON t1.Address = t2.Address AND t1.City = t2.City AND t1.State = t2.State



You really need to show us more of what you are trying to do. Your sample data that you posted in your initial post is the same as the data you just posted. So you would just do this:

SELECT * FROM Table1

Obviously that is not what you want. So please give us more information. And please always give us the DDL and DML. DDL would be the CREATE TABLE statement for the table. DML would be INSERT INTO statements for the sample data. Then provide expected result set using the sample data. Here is what it should look like:



SET NOCOUNT ON

CREATE TABLE Table1
(
Name VARCHAR(500) NOT NULL,
Address VARCHAR(500) NOT NULL,
City VARCHAR(50) NOT NULL,
State CHAR(2) NOT NULL
)

INSERT INTO Table1 VALUES('John', '1234 keel', 'abc', 'FL')
INSERT INTO Table1 VALUES('Mike', '1234 keel', 'abc', 'FL')
INSERT INTO Table1 VALUES('Andy', '52 westSt', 'abc', 'CA')
INSERT INTO Table1 VALUES('Lucy', '52 westSt', 'abc', 'CA')

SELECT t1.Name, t2.Address, t2.City, t2.State
FROM Table1 t1
INNER JOIN (
		SELECT Address, City, State
		FROM Table1
		GROUP BY Address, City, State
		HAVING COUNT(*) > 1
	   ) AS t2
ON t1.Address = t2.Address AND t1.City = t2.City AND t1.State = t2.State

DROP TABLE Table1


Name|Address|city|state

John|1234 keel|abc|FL
Mike|1234 keel|abc|FL
Andy|52 westSt|abc|CA
Lucy|52 westSt|abc|CA





So please change my code around so that we can help you.

Tara
Go to Top of Page

AsimKhaliq
Yak Posting Veteran

94 Posts
Posted - 2004-03-15 : 19:13:26
Thnks
That was so closed I just change something and also insert some rows but data is not accurate. Can u plz chk for me


CREATE TABLE Table1
(
Name VARCHAR(500) NOT NULL,
Address VARCHAR(500) NOT NULL,
City VARCHAR(50) NOT NULL,
State CHAR(2) NOT NULL
)

select * from table1

INSERT INTO Table1 VALUES('John', '1234 keel', 'abc', 'FL')
INSERT INTO Table1 VALUES('Mike', '1234 keel', 'abc', 'FL')
INSERT INTO Table1 VALUES('Andy', '52 westSt', 'abc', 'CA')
INSERT INTO Table1 VALUES('Lucy', '52 westSt', 'abc', 'CA')
INSERT INTO Table1 VALUES('John1', '123ed4 keel', 'abc', 'FL')
INSERT INTO Table1 VALUES('John1', '123ed4 keel', 'abc', 'FL')
INSERT INTO Table1 VALUES('Lucy', '52 westSt', 'abc', 'CA')
INSERT INTO Table1 VALUES('John', '1234 keel', 'abc', 'CA')
INSERT INTO Table1 VALUES('John', '1234 keel', 'abc', 'CA')

delete from table1 where name='John1'

SELECT t1.Name, t1.Address, t1.City, t2.State
FROM Table1 t1
INNER JOIN (
SELECT Address, State
FROM Table1
GROUP BY Address, State
HAVING COUNT(*) > 1
) AS t2
ON left(t1.Address,3) = left(t2.Address,3) AND t1.State = t2.State order by t1.address,name

DROP TABLE Table1
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-03-15 : 19:18:55
[code]

SELECT DISTINCT t1.Name, t1.Address, t1.City, t2.State
FROM Table1 t1
INNER JOIN (
SELECT Address, State
FROM Table1
GROUP BY Address, State
) AS t2
ON t1.Address = t2.Address AND t1.State = t2.State
ORDER BY t1.Address, Name

[/code]

Tara
Go to Top of Page

AsimKhaliq
Yak Posting Veteran

94 Posts
Posted - 2004-03-15 : 19:29:53
Thnx
This is working and I used distinct too but if u see john1 and john have more than one entry with same data. So can u retrieve that rows too.

Thnx again
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2004-03-15 : 19:37:21
[code]

SET NOCOUNT ON

CREATE TABLE Table1
(
Name VARCHAR(500) NOT NULL,
Address VARCHAR(500) NOT NULL,
City VARCHAR(50) NOT NULL,
State CHAR(2) NOT NULL
)

INSERT INTO Table1 VALUES('John', '1234 keel', 'abc', 'FL')
INSERT INTO Table1 VALUES('Mike', '1234 keel', 'abc', 'FL')
INSERT INTO Table1 VALUES('Andy', '52 westSt', 'abc', 'CA')
INSERT INTO Table1 VALUES('Lucy', '52 westSt', 'abc', 'CA')
INSERT INTO Table1 VALUES('John1', '123ed4 keel', 'abc', 'FL')
INSERT INTO Table1 VALUES('John1', '123ed4 keel', 'abc', 'FL')
INSERT INTO Table1 VALUES('Lucy', '52 westSt', 'abc', 'CA')
INSERT INTO Table1 VALUES('John', '1234 keel', 'abc', 'CA')
INSERT INTO Table1 VALUES('John', '1234 keel', 'abc', 'CA')
INSERT INTO Table1 VALUES('Tara', '123 Main Street', 'San Diego', 'CA')

SELECT t1.Name, t1.Address, t1.City, t2.State
FROM Table1 t1
INNER JOIN (
SELECT Address, State
FROM Table1
GROUP BY Address, State
HAVING COUNT(*) > 1
) AS t2
ON t1.Address = t2.Address AND t1.State = t2.State
ORDER BY t1.Address, Name

DROP TABLE Table1

[/code]

Tara
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site