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)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
