Get Duplicate value rows - complicate

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

Get Duplicate value rows - complicate

Author

Topic

svsv01
Starting Member

5 Posts

Posted - 2008-03-06 : 22:01:05

Hi,

I need some help to solve the following query.

Consider the following table
NAME OTHERNAME
---------------------
APPLE-----AA
APPLE-----AA
APPLE-----AA
ORANGE-----OO
ORANGE-----O1
GRAPE-----G1
GRAPE-----G1
GRAPE-----G2
GRAPE-----G3
MANGO-----M1
MANGO-----M2
MANGO-----M3

OUTPUT should be
----------------
ORANGE-----OO
ORANGE-----O1
GRAPE-----G1
GRAPE-----G2
GRAPE-----G3
MANGO-----M1
MANGO-----M2
MANGO-----M3

Advance Thanks for your help.
Sri

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 22:04:26

[code]
Create Table #t ([name] char(6) not null, [other] char(2) not null)

Insert Into #t
Select 'APPLE','AA' UNION ALL
Select 'APPLE','AA' UNION ALL
Select 'APPLE','AA' UNION ALL
Select 'ORANGE','OO' UNION ALL
Select 'ORANGE','O1' UNION ALL
Select 'GRAPE','G1' UNION ALL
Select 'GRAPE','G1' UNION ALL
Select 'GRAPE','G2' UNION ALL
Select 'GRAPE','G3' UNION ALL
Select 'MANGO','M1' UNION ALL
Select 'MANGO','M2' UNION ALL
Select 'MANGO','M3'

Select #t.[Name],#t.Other
FROM #t
Group by #t.[Name], #t.Other
having Count(*) = 1

drop table #t

/*Results
GRAPE G2
GRAPE G3
MANGO M1
MANGO M2
MANGO M3
ORANGE O1
ORANGE OO
[/code]

Do you need to exclude APPLE?

you don't really specify your requirements, but that should do it.

Edited to show specific requested results.

Poor planning on your part does not constitute an emergency on my part.

svsv01
Starting Member

5 Posts

Posted - 2008-03-06 : 22:28:24

Thanks for a quick reply. in your output you didn't grt GRAPE-----G1. I will put my question again. Basically get all the NAME which has more than one OTHERNAME. APPLE needs to be excluded as it has same NAME and same OTHERNAME.
NAME OTHERNAME
---------------------
APPLE-----AA
APPLE-----AA
APPLE-----AA
ORANGE-----OO
ORANGE-----O1
GRAPE-----G1
GRAPE-----G1
GRAPE-----G2
GRAPE-----G3
MANGO-----M1
MANGO-----M2
MANGO-----M3

OUTPUT 1 should be
----------------
ORANGE-----OO
ORANGE-----O1
GRAPE-----G1
GRAPE-----G2
GRAPE-----G3
MANGO-----M1
MANGO-----M2
MANGO-----M3

OUTPUT 2 should be
----------------
ORANGE
GRAPE
MANGO

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 22:38:40

You still need to clarify, as your description of what is needed, your sample data, and your expected output don't correspond.

You eliminate all of the APPLE with duplicate values


APPLE-----AA
APPLE-----AA
APPLE-----AA

But you don't elimate both GRAPE meeting the logically equivalent criteria.


GRAPE-----G1
GRAPE-----G1

Therefore, my code provides you with a working solution to eliminate where there are duplicates based on [Name] and Duplicate [Other]

here it is again, this time with a results showing the duplicate counts based on your provided sample. Note that the 3rd sample still provides expected results because there ARE values of GRAPE returned in the 1st criteria which have unique and only 1 count of [Other]


Create Table #t ([name] char(6) not null, [other] char(2) not null)

Insert Into #t
Select 'APPLE','AA' UNION ALL
Select 'APPLE','AA' UNION ALL
Select 'APPLE','AA' UNION ALL
Select 'ORANGE','OO' UNION ALL
Select 'ORANGE','O1' UNION ALL
Select 'GRAPE','G1' UNION ALL
Select 'GRAPE','G1' UNION ALL
Select 'GRAPE','G2' UNION ALL
Select 'GRAPE','G3' UNION ALL
Select 'MANGO','M1' UNION ALL
Select 'MANGO','M2' UNION ALL
Select 'MANGO','M3'


Select Count(*),
#t.[Name],#t.Other
FROM #t  
Group by #t.[Name], #t.Other
/*Results
3	APPLE 	AA
2	GRAPE 	G1
1	GRAPE 	G2
1	GRAPE 	G3
1	MANGO 	M1
1	MANGO 	M2
1	MANGO 	M3
1	ORANGE	O1
1	ORANGE	OO
*/

Select #t.[Name],#t.Other
FROM #t  
Group by #t.[Name], #t.Other
having Count(*) = 1


/*Results
GRAPE 	G2
GRAPE 	G3
MANGO 	M1
MANGO 	M2
MANGO 	M3
ORANGE	O1
ORANGE	OO */

Select Distinct v.Name FROM (Select #t.[Name],#t.Other
							FROM #t  
							Group by #t.[Name], #t.Other
							having Count(*) = 1) v



/*Results
GRAPE 
MANGO 
ORANGE

*/
drop table #t

Poor planning on your part does not constitute an emergency on my part.

svsv01
Starting Member

5 Posts

Posted - 2008-03-06 : 22:45:20

Hi, Thanks a lot for a very prompt reply.

I need to get GRAPE----G1 also. The reason is GRAPE has OTHERNAME G1, G2 & G3. APPLE is eleminated because APPLE has one and only other name AA. please let me know if you need more info. Again thankf for your help.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 23:15:45

sloppy, but this does it.


Create Table #t ([name] char(6) not null, [other] char(2) not null)

Insert Into #t
Select 'APPLE','AA' UNION ALL
Select 'APPLE','AA' UNION ALL
Select 'APPLE','AA' UNION ALL
Select 'ORANGE','OO' UNION ALL
Select 'ORANGE','O1' UNION ALL
Select 'GRAPE','G1' UNION ALL
Select 'GRAPE','G1' UNION ALL
Select 'GRAPE','G2' UNION ALL
Select 'GRAPE','G3' UNION ALL
Select 'MANGO','M1' UNION ALL
Select 'MANGO','M2' UNION ALL
Select 'MANGO','M3'


Select Distinct #t.[name],#t.[other]
from #T INNER JOIN	(Select name,Count(name) as Ct 
					 FROM  (Select DISTINCT name, Other
							FROM #t) a
					 group by name) B

ON #t.[name] = b.name
Where b.CT > 1

/*results
GRAPE 	G1
GRAPE 	G2
GRAPE 	G3
MANGO 	M1
MANGO 	M2
MANGO 	M3
ORANGE	O1
ORANGE	OO
*/

drop table #t

Poor planning on your part does not constitute an emergency on my part.

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-07 : 05:28:21

Hi,

Try with the following

SELECT DISTINCT a.[name], a.other
FROM #t a
INNER JOIN (SELECT DISTINCT name ,other, RANK() OVER (partition BY name ORDER BY other Desc ) AS cnt FROM #t ) b
ON a.name = b.name
WHERE b.cnt > 1

OR

SELECT DISTINCT a.[name], a.other
FROM #t a
INNER JOIN (SELECT DISTINCT name , COUNT (DISTINCT Other) AS cnt FROM #t GROUP BY name ) b
ON a.name = b.name
WHERE b.cnt > 1

svsv01
Starting Member

5 Posts

Posted - 2008-03-07 : 07:08:59

quote:

Originally posted by dataguru1971

sloppy, but this does it.


Create Table #t ([name] char(6) not null, [other] char(2) not null)

Insert Into #t
Select 'APPLE','AA' UNION ALL
Select 'APPLE','AA' UNION ALL
Select 'APPLE','AA' UNION ALL
Select 'ORANGE','OO' UNION ALL
Select 'ORANGE','O1' UNION ALL
Select 'GRAPE','G1' UNION ALL
Select 'GRAPE','G1' UNION ALL
Select 'GRAPE','G2' UNION ALL
Select 'GRAPE','G3' UNION ALL
Select 'MANGO','M1' UNION ALL
Select 'MANGO','M2' UNION ALL
Select 'MANGO','M3'


Select Distinct #t.[name],#t.[other]
from #T INNER JOIN	(Select name,Count(name) as Ct 
					 FROM  (Select DISTINCT name, Other
							FROM #t) a
					 group by name) B

ON #t.[name] = b.name
Where b.CT > 1

/*results
GRAPE 	G1
GRAPE 	G2
GRAPE 	G3
MANGO 	M1
MANGO 	M2
MANGO 	M3
ORANGE	O1
ORANGE	OO
*/

drop table #t

Poor planning on your part does not constitute an emergency on my part.

The query was simply Great. It worked. Thanks for all your help. The reply was so timely and very helpful.

svsv01
Starting Member

5 Posts

Posted - 2008-03-07 : 08:04:39

quote:
Originally posted by ranganath

Hi,

Try with the following

SELECT DISTINCT a.[name], a.other
FROM #t a
INNER JOIN (SELECT DISTINCT name ,other, RANK() OVER (partition BY name ORDER BY other Desc ) AS cnt FROM #t ) b
ON a.name = b.name
WHERE b.cnt > 1

OR

SELECT DISTINCT a.[name], a.other
FROM #t a
INNER JOIN (SELECT DISTINCT name , COUNT (DISTINCT Other) AS cnt FROM #t GROUP BY name ) b
ON a.name = b.name
WHERE b.cnt > 1

Thank for the help. The query works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources