OT: MySQL question

Hi,

Sorry about the off topic post, but my news server doesn't carry any
decent MySQL newsgroups and it seems that many people using PHP are
also using MySQL so I thought someone might know.

My question concerns finding duplicates in a field in my database
table. The field contains a URL and I can see that there are some
duplicates as count(URI) and count(distinct URI) give different
results. What I wondered is if there is a way for me to list only
these duplicates. Something along the lines of the following, although
this doesn't work

select count(URI),URI from main group by URI where count(URI) > 1

Any ideas?

tia

Chris

On Thu, 10 Jul 2003 11:43:54 +0100, Chris wrote:
> select count(URI),URI from main group by URI where count(URI) > 1

Shouldn't this be:

select count(URI) as cnt,URI from main group by URI where cnt > 1

or more likely

select count(URI),URI from main group by URI having count(URI) > 1

Cheers,


Andy

On Thu, 10 Jul 2003 12:50:41 +0100, "Andy Jeffries"
<news@andyjeffries.remove.co.uk> wrote:


>select count(URI),URI from main group by URI having count(URI) > 1
>

You're a star mate. That worked a treat.

Cheers,

C