Get rows with duplicate values in certain columns

[agekay@gmx.de]

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.

[Simon Hayes]

Re: Get rows with duplicate values in certain columns


<agekay@gmx.d e> wrote in message
news:1120392807 .001826.295060@ g49g2000cwa.goo glegroups.com.. .[color=blue]
> 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.
>[/color]

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

[agekay@gmx.de]

Re: Get rows with duplicate values in certain columns

Thank you so much! That's exactly what I was looking for. Works like a
charm!