RE: [PHP-DB] non-unique records
| From: | Rick Emery | Date: | Mon, 11 Feb 2002 22:17:44 +0000 |
| Subject: | RE: [PHP-DB] non-unique records | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-16833@lists.php.net to get a copy of this message | ||
Richard Emery
IT Sr. Project Manager
(972) 478-3398
-----Original Message-----
From: Rick Emery
Sent: Monday, February 11, 2002 4:15 PM
To: 'bill'
Cc: php-db@lists.php.net
Subject: RE: [PHP-DB] non-unique records
SELECT myfield,COUNT(myfield) AS mycount FROM mytable GROUP BY myfield
HAVING mycount>1;
This provides table with 2 columns, the second column contains counts.
Therefore, where the count > 1 for a value in the first column, then that
field value is not unique. Example follows:
mysql> describe aa;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> select * from aa;
+------+
| x |
+------+
| 1 |
| 1 |
| 1 |
| 2 |
| 3 |
| 3 |
+------+
6 rows in set (0.00 sec)
mysql> select x,count(x) as ctr from aa group by x;
+------+-----+
| x | ctr |
+------+-----+
| 1 | 3 |
| 2 | 1 |
| 3 | 2 |
+------+-----+
3 rows in set (0.00 sec)
You note that there are three 1s, one 2, two 3s
mysql> select x,count(x) AS ctr FROM aa GROUP BY x HAVING ctr>1;
+------+-----+
| x | ctr |
+------+-----+
| 1 | 3 |
| 3 | 2 |
+------+-----+
2 rows in set (0.00 sec)
-----Original Message-----
From: bill [mailto:bill@billtron.com]
Sent: Monday, February 11, 2002 4:07 PM
To: Rick Emery
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] non-unique records
Hmmm, so ...
SELECT COUNT(myfield) AS mycount
FROM mytable
WHERE mycount > 1
would work?
Rick Emery wrote:
> SELECT COUNT(myfield) AS mycount FROM mytable;
>
> If mycount > 1, then the field value is not unique
>
> -----Original Message-----
> From: bill [mailto:bill@billtron.com]
> Sent: Monday, February 11, 2002 3:49 PM
> To: php-db@lists.php.net
> Subject: [PHP-DB] non-unique records
>
> How can I find records that do -not- have a unique value in a field?
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php