Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 15 of 15 total |
Looking for a clever sql |
Tue, Apr 13 2010 6:49 AM | Permanent Link |
John Hay | Sori
> I have a table with Field1, Field2 > Some records have a equal values on the first 2 fields. > Rec1.Field1 = Rec2.Field1; > Rec1.Field2 = Rec2.Field2; > > I want to remain only with the first record and delete the others. I was going to suggest DELETE FROM Table WHERE Id NOT IN (SELECT min(Id) FROM Table GROUP BY Field1,Field2) It looks legitimate but produces an error ElevateDB Error #700 An error was found in the statement at line 0 and column 0 (Invalid expression "MIN of Id" found, ORDER BY clause not allowed) Using the same query with a derived table does seem to work DELETE FROM Table WHERE Id NOT IN (SELECT DeleteId FROM (SELECT min(Id) AS DeleteId FROM Table GROUP BY Field1,Field2) t1) John |
Tue, Apr 13 2010 7:41 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
>DELETE FROM Table WHERE Id NOT IN >(SELECT DeleteId FROM (SELECT min(Id) AS DeleteId FROM Table >GROUP BY Field1,Field2) t1) That's one of the ways I thought about but (and its a great big BUT) I think that will result in all singleton records being deleted as well and leaving only the first occurrence of duplicate/triplicate etc records. Roy Lambert |
Tue, Apr 13 2010 9:58 AM | Permanent Link |
John Hay | John > > >DELETE FROM Table WHERE Id NOT IN > >(SELECT DeleteId FROM (SELECT min(Id) AS DeleteId FROM Table > >GROUP BY Field1,Field2) t1) > > That's one of the ways I thought about but (and its a great big BUT) I think that will result in all singleton records being deleted as well and leaving only the first occurrence of duplicate/triplicate etc records. I don't think so. We are selecting for deletion only those records which do not have the smallest id. If we had only 1 record in the table it would not be deleted. John |
Tue, Apr 13 2010 11:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
>I don't think so. We are selecting for deletion only those records which do >not have the smallest id. > >If we had only 1 record in the table it would not be deleted. Very good point John. I think that having just sorted out my deduping stuff my brain wouldn't encompass something that simple Roy Lambert |
Wed, Apr 14 2010 5:55 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | John,
<< It looks legitimate but produces an error >> I'll check this out and make sure that it is fixed. -- Tim Young Elevate Software www.elevatesoft.com |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |