Microsoft SQL Server forum - Get rows with duplicate values in certain columns

Author

Get rows with duplicate values in certain columns

agekay@gmx.de

2005-07-03, 7:23 am

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

2005-07-03, 9:23 am

<agekay@gmx.de> wrote in message
news:1120392807.001826.295060@g49g2000cwa.googlegroups.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

agekay@gmx.de

2005-07-03, 1:23 pm

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

Sponsored Links