Hi, thanks, I need to look at this some more but I think it may do the trick. Gordon. On Tue, 2005-10-11 at 09:17 +0100, Richard Grieve wrote:
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
!DSPAM:434b7bd57312068195507!