Display Duplicate Records

[egodette]

I want to display information if there is a duplicate in one field. I know how to find the duplicate by using GROUP By and HAVING but how do I get other information to show up?

DATA:
AAPL AAPL 1000
ABT AAPL 1100
BBVY STUB 1200
CCDG AWER 1200

Desired OUTPUT:
AAPL AAPL 1000
ABT AAPL 1100

This statement give the right duplicate information. Only concerned about <field2>.
Select <field2> from <table> group by <field2> having count(<field2>) > 1

so how do I get the desired output as show above? If I include <field1> in the select the group by no longer produces a count greater than 1.

Thanks.

 

[bborissov]

Use your query as a derived table and JOIN it to your main table

SELECT YourTable.*
FROM YourTable
INNER JOIN (SELECT Field2 
                  FROM YourTable 
            GROUP BY Field2
            HAVING COUNT(*) > 1) Tbl1
      ON YourTable.Field2 = Tbl1.Field2

Borislav Borissov
VFP9 SP2, SQL Server