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.

"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

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

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
>
>
>

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.

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.
>

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.
>

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

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
>
>

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