471,049 Members | 1,421 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

Get rows with duplicate values in certain columns

Hi there,

I would like to know how to get rows with duplicate values in certain
columns. Let's say I have a table called "Songs" with the following
columns:

artist
album
title
genre
track

Now I would like to show the duplicate songs to the user. I consider
songs that have the same artist and the same title to be the same song.
Note: All columns do not have to be the same.

How would I accomplish that with SQL in SQL Server?

Thanks to everyone reading this. I hope somebody has an answer. I've
already searched the whole newsgroups, but couldn't find the solution.

Jul 23 '05 #1
2 1796

<ag****@gmx.de> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Hi there,

I would like to know how to get rows with duplicate values in certain
columns. Let's say I have a table called "Songs" with the following
columns:

artist
album
title
genre
track

Now I would like to show the duplicate songs to the user. I consider
songs that have the same artist and the same title to be the same song.
Note: All columns do not have to be the same.

How would I accomplish that with SQL in SQL Server?

Thanks to everyone reading this. I hope somebody has an answer. I've
already searched the whole newsgroups, but couldn't find the solution.


You probably need something like this:

select
s.artist,
s.album,
s.title,
s.genre,
s.track
from
dbo.Songs s
join
(
select
artist,
title
from
dbo.Songs
group by
artist,
title
having count(*) > 1
) dt
on s.artist = dt.artist and
s.title = dt.title

If this doesn't give the results you expect, then you should post some DDL
and sample data to clarify exactly what you need:

http://www.aspfaq.com/etiquette.asp?id=5006

Simon
Jul 23 '05 #2
Thank you so much! That's exactly what I was looking for. Works like a
charm!

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by g_chime | last post: by
68 posts views Thread by Martin Joergensen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.