Duplicate entries in multiple columns

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

Transact-SQL (2005)

Duplicate entries in multiple columns

Author

Topic

sedwick1024
Starting Member

6 Posts

Posted - 2007-02-06 : 12:46:44

I am trying to find mulitple records that have the same set of values in two columns. For example, in a Books table with Author and Genre columns, I want to return all records that share the same Author and Genre. If I had the following records:

_Author_ _Genre_
Bradbury Drama
Bradbury Science Fiction
Clancy Suspense
Steele Romance
Bradbury Science Fiction
Clancy Suspense
Steele Autobiography
Bradbury Science Fiction
Steele Romance
Clancy Drama
Bradbury Romance

I'd want the following results:

_Author_ _Genre_
Bradbury Science Fiction
Clancy Suspense
Steele Romance

Your help is graciously appreciated in advance.

--Sedwick

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 13:10:31

select author, max(genre) from table1 group by author

Peter Larsson
Helsingborg, Sweden

sedwick1024
Starting Member

6 Posts

Posted - 2007-02-06 : 13:30:25

Eh, not quite. I don't want to limit it to the most matches per Author, as an author may have done multiple works in another genre as well, something I apologize for not making clear. If I had multiple records with these values:

_Author_ _Genre_
Bradbury Comedy

I'd want that to also show up with the sample results I gave.

--Sedwick

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 14:06:13

My suggestion do not return the "most matches". It returns the MAX genre (alphabetically) for each author.

This query, however, returns all multiples in the books table.

SELECT author, genre
from Books group by author, genre having count(*) > 1

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-06 : 14:11:28

I don't get it..what causes the other rows, like Bradbury Drama top be excluded

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sedwick1024
Starting Member

6 Posts

Posted - 2007-02-06 : 15:00:26

There's only one record that has Bradbury as the author and Drama as the Genre. Meanwhile, there are 3 records that have Bradbury as the author and Science Fiction as the Genre, so it would show up.

--Sedwick

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-06 : 15:27:36

So it's whatever they are found the most in?

Is this homework?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sedwick1024
Starting Member

6 Posts

Posted - 2007-02-06 : 15:38:39

No, it's for jobwork. I just need to know which author/genre combinations show up in multiple records. Peso's last post seems to have done it though, so thanks!

--Sedwick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources