how to find duplicate records

[tppradeep18]

I have a table which looks as follows

Field1 Field2 Field3
A aa 1
A aa 1
A aa 1
B bb 1

How do I write a query that would return me a result as follows

Field1 Field2 Field3
A aa 1
B bb 1

Please help me. Thanks

 

[Sylv4n]

Use the following SQL Code worked for me, you need a SELECT DISTINCTROW

SELECT DISTINCTROW tblDetail.Code, tblDetail.Value
FROM tblDetail

 

[tppradeep18]

thanks Sylv4n for that tip. Now I have another problem. what if I have the following data

Field1 Field2 Field3
A aa 1
A aa 2
A aa 3
B bb 4


As you see the distinctrow is working when data in all the fields are the same. I want to search for a unique value in say Field2 and return me the following result


Field1 Field2 Field3
A aa 1
B bb 4

Thanks

 

[quatermass]

This is taken from another answer on this site, but I cannot remember who (sorry, whoever it is )


You can try something like:

SELECT A.tableX INTO Duplicates_Table
FROM tableX A, (SELECT Field1+Field2 as matchfield, Min(Field3) as Min_Id
FROM tableX
GROUP BY Field1+Field2) B
WHERE A.Field1+A.Field2 = B.matchfield
AND A.Field3 <> B.Min_Id


It would be better if you could have the first two fields concatenated into another field which could then be indexed for speed.

Kevin **************************************************************
The difference between fiction and reality is that fiction has to make sense.
**************************************************************

 

[sdimaggio]

Where would I put this code when I am in the detail section of the query?

SELECT DISTINCTROW tblDetail.Code, tblDetail.Value
FROM tblDetail