Get Duplicate Records with Primary Key

iam using sqso I have 3 fields:

ID        CustomerNo        Date
1           0009                9/9/2011
2           0009                9/9/2011
3           0009                9/9/2011
4           0010                9/9/2011

i want to determine the records with the same customerno and date and get each of their unique id.

ID        CustomerNo        Date
1           0009                9/9/2011
2           0009                9/9/2011
3           0009                9/9/2011

How can I do this? i tried this code:

SELECT DISTINCT S1.ID,  S1.customerno, S1.DATE
FROM TABLE AS S1
INNER JOIN TABLE AS S2
ON  S1.DATE=S2.DATE AND S1.CUSTOMERNO=S2.CUSTOMERNO 
AND S2.id <= S1.id
GROUP BY S1.customerno, S1.id , S1.DATE

but it also get the record no.4, different customer no bu开发者_JAVA百科t with the same date.

i want to get only those that are duplicate in customerno and date.

If I understand you correctly, the duplicate customer numbers and dates are given by this query. (My table is just named "t"; I can't use "table" as a table name.)

select customerno, date
from t
group by customerno, date
having count(*) >= 2

To get the ID numbers for those "duplicated" customers, join that to the original table.

select t.id, t.customerno, t.date
from t
inner join
    (select customerno, date
     from t
     group by customerno, date
     having count(*) >= 2) dups 
  on dups.customerno = t.customerno and dups.date = t.date

SELECT 
    DISTINCT S1.ID,  S1.customerno, S1.DATE 
FROM TABLE AS S1 
INNER JOIN TABLE AS S2 ON S1.DATE = S2.DATE 
WHERE 
    S1.DATE = S2.DATE 
    AND S1.CUSTOMERNO = S2.CUSTOMERNO 
    AND S2.id <= S1.id 
GROUP BY 
    S1.customerno, S1.id , S1.DATE

Try this code

If "CustomerNo" is the unique id you are talking about then this query may work for you.

Select Count(*) as #OfDuplicates, CustomerNo, Date
From Table
Group By CustomerNo, Date
Having Count(*)>1

Or this query can be used if you would like the last row's key as a sample reference. This also avoids using a subquery.

Select Count(*) as #OfDuplicates, CustomerNo, Date, Max(Id) as SampleInstanceId
From Table
Group By CustomerNo, Date
Having Count(*)>1