> Philip Hallstrom wrote:
>>>> Hi all, a though query problem for me...
>>>>
>>>> I have a table with 2 rows that matter: url and id
>>>>
>>>> If url and id are the same in 2 rows, then that's no good (bad data).
>>>>
>>>> I need to find all the rows that are duplicates. I can't think of how
>>>> to approach the sql for this.. any pointers?
>>>>
>>>
>>> Select COUNT(*) as num_entries, url from table WHERE num_entries>1 GROUP
>>> BY url
>>>
>>> Untested, but the concept should work for you.
>>
>> That would mark rows that have different id's, but the same url...
>
> No it wouldn't, he's grouping by the url.
Yes, it would. As your example shows. Parent said "If url and id are the
same in 2 rows". In your example you have 1/google and 2/google and that
is showing up in your final query.
Which parent doesn't want.. unless I'm mis-reading his request...
>
> mysql> create table a(a int, b text);
> Query OK, 0 rows affected (0.02 sec)
>
> mysql> insert into a(a, b) values (1, 'google.com');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into a(a, b) values (2, 'google.com');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into a(a, b) values (3, 'yahoo.com');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select count(*) as num, b from a group by b;
> +-----+------------+
> | num | b |
> +-----+------------+
> | 2 | google.com |
> | 1 | yahoo.com |
> +-----+------------+
> 2 rows in set (0.00 sec)
>
>
> The final query should be:
>
> mysql> select count(*) as num, b from a group by b having count(*) > 1;
> +-----+------------+
> | num | b |
> +-----+------------+
> | 2 | google.com |
> +-----+------------+
> 1 row in set (0.00 sec)
>
