Gordon I just used this to clean some dirty data in a profile_values table which I had prepopulated. Is this what you're looking for? select uid, fid, count(*) as num from profile_values where fid=72 group by uid having num >1 This returns uid fid num 63 72 2 240 72 2 242 72 2 248 72 2 298 72 2 372 72 2 496 72 2 607 72 2 614 72 2 664 72 2 770 72 2 860 72 2 1057 72 2 1592 72 2 What would be cool would be select * from profile_values where where fid=7 AND uid in (select uid from profile_values where fid=7 group by uid having count(*)>1) But since mysql won't do nested selects I then used select * from profile_values where where fid=7 AND uid in (63, 240, 242, 248 ,298, 372, 496, 607, 614, 664, 770, 860, 1057, 1592 ) Is this any good to you? Rich -----Original Message----- From: drupal-devel-bounces@drupal.org [mailto:drupal-devel-bounces@drupal.org] On Behalf Of Gordon Heydon Sent: 11 October 2005 00:11 To: drupal-devel@drupal.org Subject: Re: [drupal-devel] couple of SQL questions Hi, On Mon, 2005-10-10 at 10:05 -0400, James Walker wrote:
On 10-Oct-05, at 9:42 AM, Gordon Heydon wrote:
Hi,
I have a couple of issues that I have been playing with for a project and can't quite get my head around it from a SQL POV.
Is it possible to list all the rows in a table where one column has more than one occurrence of the same value.
In this can I am storing the ip address of some submitted data, and I need to check to see if they have submitted more than 1 row. I don't care about the ones with a single occurance, but I need to know about the ones that appear more that one.
I think you're looking for something like:
SELECT foo, bar, count(baz) as num FROM table GROUP BY foo, bar HAVING num > 1;
Thanks, this will return the consolidated list of results, but is there a way to still display the individual details. I think I may need to create a new page that will help identify dups. Thanks again. Gordon