On Sat, 12 Apr 2008 20:55:04 +0100, "Jane T" <janet@[EMAIL PROTECTED]
> wrote:
(I renamed your table to tblTest)
Here is how I solve it for simple cases involving a single field:
select reference_
from tblTest
group by reference_
having count(reference_) > 1
With more than one field, I simply concatenate those fields and then
I'm using the same method as in the first query:
select reference_ + '-' + cast(value_ as varchar)
from tblTest
group by reference_ + '-' + cast(value_ as varchar)
having count(reference_ + '-' + cast(value_ as varchar)) > 1
If you then want the entire records for the found values, then compare
the concatenated values with those of the values from the previous
query:
select *
from tblTest
where reference_ + '-' + cast(value_ as varchar) = (select
reference_ + '-' + cast(value_ as varchar)
from tblTest
group by reference_ + '-' + cast(value_ as varchar)
having count(reference_ + '-' + cast(value_ as varchar)) > 1)
-Tom.
>I want to check the data from an invoicing database for possible
duplicate
>invoices.
>
>I have created an example script below.
>
>I want to return rows where there is a match for both reference_ and
value_,
>so in this
>example in my SELECT statement I only want to return the two rows that
match
>on both
>reference_ and value_
>
>
>CREATE TABLE #TMP
>(reference_ varchar(10), suppliercode_ varchar(10), value_ money)
>
>INSERT INTO #TMP VALUES ('A1','123',100)
>INSERT INTO #TMP VALUES ('A2','234',100)
>INSERT INTO #TMP VALUES ('A1','345',100)
>INSERT INTO #TMP VALUES ('A2','234',70)
>
>SELECT * FROM #TMP ORDER BY REFERENCE_
>
|
