find duplicates query

[lorca]

Hi
I have this sql running which works ok, basially show potential duplicte customers by uprn and orders (DESC) by datelastmodified for the customer. problem is say a MR. SMITH is the first record (because somebody has just created him 2 minutes ago) his duplicate may have been created 2 months ago so in the query/report
a) because the uswer wants dat parameters it may not even show
b) if it does the user has to look through pages of records to find them

what i'd like is to keep the ordering by datelastmodfied DESC, so show the most recent entry for MR. SMITH at the top (MAX(a.datelastmodified) ?), but beneath have the duplicate also ?

any help appreciated
gaz

SELECT LEFT(a.SURNAME, 24) AS Surname, b.UPRN, a.CUSTOMERID,
a.DATELASTMODIFIED
FROM CUSTOMERS a INNER JOIN
CUSTOMERADDRESS b ON a.CUSTOMERID = b.CUSTOMER
WHERE ((a.SURNAME + ISNULL(b.UPRN, '')) IN
(SELECT a.surname + isnull(b.uprn, '')
FROM CUSTOMERS a, CUSTOMERADDRESS b
WHERE a.customerid = b.customer 
GROUP BY a.surname, b.uprn
HAVING COUNT(*) > 1))
ORDER BY a.datelastmodified, b.UPRN, LEFT(a.SURNAME, 24), a.CUSTOMERID

 

[bborissov]

Can you post an exaple records from your tables and desired results. There is something I didn't like in that query, but maybe I am complitely wrong.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[lorca]

example as is

Surname, UPRN, CustomerID, datelastmodified
Ainslie, 900012568, 2001567, 6/27/2006 2:51:27 PM
Hughes, 900011774, 2001568, 6/27/2006 2:00:15 PM
Roberts, 900001001, 2001500, 6/26/2006 1:00:00 PM
Ainslie, 900012568, 2001567, 6/01/2006 06:00:00 AM

so sorted by a.datelastmodified, b. uprn, left(a.surnmame, 24), a.customerid

I'd like to keep this sort order but my desired results are

Surname, UPRN, CustomerID, datelastmodified
Ainslie, 900012568, 2001567, 6/27/2006 2:51:27 PM
Ainslie, 900012568, 2001567, 6/01/2006 06:00:00 AM
Hughes, 900011774, 2001568, 6/27/2006 2:00:15 PM
Roberts, 900001001, 2001500, 6/26/2006 1:00:00 PM

thanks
Gaz

 

[bborissov]

Try this (not tested)

SELECT LEFT(a.SURNAME, 24) AS Surname,
       b.UPRN,
       a.CUSTOMERID,
       a.DATELASTMODIFIED
FROM CUSTOMERS
INNER JOIN 
(SELECT CUSTOMERS.SURNAME, CUSTOMERADDRESS.UpRn, CUSTOMERADDRESS.CUSTOMER
        FROM CUSTOMERS
        INNER JOIN CUSTOMERADDRESS ON CUSTOMERS.customerid = CUSTOMERADDRESS.customer
        HAVING COUNT(*) > 1
        GROUP BY SURNAME, UpRn, CUSTOMER) Tbl1
ON CUSTOMERS.CUSTOMERID = Tbl1.CUSTOMER
ORDER BY LEFT(a.SURNAME, 24), b.UPRN, a.datelastmodified DESC

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[lorca]

Hi Borislav

thanks

yeah that seems to only return one customer.

thanks anyway