Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 43 total |
Display Duplicate Records in DBISAM |
Sun, Jan 1 2006 4:23 PM | Permanent Link |
"Scott Martin" | I need to find and display duplicate records.
I am able to find the dupes using: SELECT DisplayName,COUNT(*) FROM Clients GROUP BY DisplayName HAVING COUNT(*) > 1 Trying to Display both duplicates using: SELECT DisplayName,COUNT(*) FROM Clients WHERE DisplayName IN ( SELECT DisplayName,COUNT(*) FROM Clients GROUP BY DisplayName HAVING COUNT(*) > 1 ) I get the error of more than 1 column in subselect. If I remove the COUNT from the column list, I get an error that HAVING clause not found in source columns. It seems the DBISAM requires the COUNT(*) to be in the column list and others do not as I am able to perform these in MySQL SELECT DisplayName FROM Clients GROUP BY DisplayName HAVING COUNT(*) > 1 SELECT DisplayName FROM Clients WHERE DisplayName IN ( SELECT DisplayName FROM Clients GROUP BY DisplayName HAVING COUNT(*) > 1 ) What is needed to get this to work? Regards, Scott. |
Sun, Jan 1 2006 10:38 PM | Permanent Link |
"Robert" | "Scott Martin" <scottmartin@pdq.net> wrote in message news:E203C5CA-468B-4264-8040-1E05D32B53A5@news.elevatesoft.com... > I need to find and display duplicate records. > > Trying to Display both duplicates using: > > SELECT DisplayName,COUNT(*) > FROM Clients > WHERE DisplayName > IN ( SELECT DisplayName,COUNT(*) FROM Clients GROUP BY DisplayName HAVING COUNT(*) > 1 ) > > I get the error of more than 1 column in subselect. > Well, you ARE selecting two columns. Two possiblities both untested but should be close 1. Make a memory table with the subquery SELECT DisplayName Dupname, COUNT(*) into memory\temp FROM Clients GROUP BY DisplayName HAVING COUNT(*) > 1 ; SELECT DisplayName from Clients join memory\temp on DisplayName = Dupname; 2. Use two tables (assuming you have some unique ID field) SELECT DISTINCT DisplayName from Clients C1 JOIN Clients C2 on (c1.DisplayName = c2.DisplayName and c1.ID <> c2.ID) Robert |
Mon, Jan 2 2006 6:17 AM | Permanent Link |
"Ole Willy Tuv" | Scott,
<< It seems the DBISAM requires the COUNT(*) to be in the column list and others do not as I am able to perform these in MySQL SELECT DisplayName FROM Clients GROUP BY DisplayName HAVING COUNT(*) > 1 >> The query is correct SQL and should work. There's obviously a flaw in the implementation of the HAVING clause in DBISAM. Ole Willy Tuv |
Mon, Jan 2 2006 1:52 PM | Permanent Link |
"Scott Martin" | I am due to the fact that DBISAM barks if I do not.. a flaw? as pointed out by Ole Willy Tuv.
Perhaps Tim can give this a look. If not, then I may have to resort to using a temp table. Regards. Scott, > > Well, you ARE selecting two columns. > > Two possiblities both untested but should be close > > 1. Make a memory table with the subquery > > SELECT DisplayName Dupname, COUNT(*) into memory\temp FROM Clients GROUP BY > DisplayName HAVING COUNT(*) > 1 ; > SELECT DisplayName from Clients > join memory\temp on DisplayName = Dupname; > > 2. Use two tables (assuming you have some unique ID field) > > SELECT DISTINCT DisplayName from Clients C1 > JOIN Clients C2 on (c1.DisplayName = c2.DisplayName and c1.ID <> c2.ID) > > Robert > > > |
Mon, Jan 2 2006 4:40 PM | Permanent Link |
"Adam H." | Hi Scott,
As an alternative, you could try: SELECT DisplayName Dupname, COUNT(*) as DupCount into memory\temp FROM Clients GROUP BY DisplayName ; SELECT DisplayName from Clients join memory\temp temp on DisplayName = Dupname where temp.DupCount > 1; Regards Adam. |
Tue, Jan 3 2006 11:06 AM | Permanent Link |
"Scott Martin" | Adam,
Thank you very much! This will do until there is a fix. Regards, Scott. "Adam H." <info at wsd.net .au> wrote in message news:10151B01-A91C-480A-B1CD-EBF7E8208D08@news.elevatesoft.com... > Hi Scott, > > As an alternative, you could try: > > SELECT DisplayName Dupname, COUNT(*) as DupCount into memory\temp FROM Clients GROUP BY > DisplayName ; > SELECT DisplayName from Clients > join memory\temp temp on DisplayName = Dupname > where temp.DupCount > 1; > > Regards > > Adam. > |
Tue, Jan 3 2006 1:25 PM | Permanent Link |
"Donat Hebert \(WSI\)" | Select DisplayName, Count(*) as DupRecs
from Clients group by Displayname having DupRecs > 1 works in one step... "Adam H." <info at wsd.net .au> wrote in message news:10151B01-A91C-480A-B1CD-EBF7E8208D08@news.elevatesoft.com... > Hi Scott, > > As an alternative, you could try: > > SELECT DisplayName Dupname, COUNT(*) as DupCount into memory\temp FROM > Clients GROUP BY > DisplayName ; > SELECT DisplayName from Clients > join memory\temp temp on DisplayName = Dupname > where temp.DupCount > 1; > > Regards > > Adam. > |
Tue, Jan 3 2006 2:16 PM | Permanent Link |
"Ole Willy Tuv" | Donat,
> Select DisplayName, Count(*) as DupRecs > from Clients > group by Displayname > having DupRecs > 1 That's actually invalid SQL. Yes, it works in DBISAM, but try it in SQL Server, Interbase etc. Ole Willy Tuv |
Tue, Jan 3 2006 3:17 PM | Permanent Link |
"Donat Hebert \(WSI\)" | True as DBISAM allows us to reference the column alias vs the function (A
feature I really like btw) however to make it standard and valid, the following works on all environments ... Sql server is more tolerant as it works whether the Count(*) is stated in the select or not however it doesn't mind having it there .. Select CostCentre, count(*) as NumDups from Bgsum group by CostCentre Having Count(*) > 1; Donat. "Ole Willy Tuv" <owtuv@online.no> wrote in message news:154F9CAA-D94C-473B-9E44-596DE0E98502@news.elevatesoft.com... > Donat, > >> Select DisplayName, Count(*) as DupRecs >> from Clients >> group by Displayname >> having DupRecs > 1 > > That's actually invalid SQL. Yes, it works in DBISAM, but try it in SQL > Server, Interbase etc. > > Ole Willy Tuv > > |
Tue, Jan 3 2006 4:08 PM | Permanent Link |
"Ole Willy Tuv" | Donat,
<< True as DBISAM allows us to reference the column alias vs the function (A feature I really like btw) >> Yes, that could work as a proprietary extension if only select list column names pointing to columns or expressions that are valid in the HAVING clause were allowed. However, it seems that DBISAM allows any select list column to be specified, e.g. the following revised version of your query works: Select CostCentre, 1 as Dummy, count(*) as NumDups from Bgsum group by CostCentre Having Dummy >= 1 This indicates that DBISAM is processing the HAVING clause against the result set columns and data, which is probably why it barks about the aggregate function missing in the select list in Scott's original query. The HAVING clause is a filter on the groups of rows resulting from evaluating the FROM and GROUP BY clauses, and can only specify grouping columns and aggregate functions. << Sql server is more tolerant as it works whether the Count(*) is stated in the select or not however it doesn't mind having it there .. >> Yes, because it's not required. All engines I know of would execute the following query just fine: Select CostCentre from Bgsum group by CostCentre Having Count(*) > 1 Ole |
Page 1 of 5 | Next Page » | |
Jump to Page: 1 2 3 4 5 |
This web page was last updated on Thursday, May 23, 2024 at 03:39 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |