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.<agekay@.gmx.de> wrote in message
news:1120392807.001826.295060@.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|||Thank you so much! That's exactly what I was looking for. Works like a
charm!