-2

I have a table that reports all labels placed in a set of drawings. Each label has a file name and drawing number. I want to filter this table for all entries of a certain file name and duplicates of the drawing number. This will tell me which drawings have multiples of the same label. For example, the picture in the link below shows that I have three of Label 1 in DWG#1 and two of label #1 in DWG#2. This tells me that I need to clean up the duplicates in these two drawings. I need a statement to run in SQL Server to report these entries. Thanks!

Example

Jande
  • 1,695
  • 2
  • 20
  • 32
rfarmer22
  • 1
  • 2
  • 2
    Providing sample data in the form of an image is discouraged here is SO. Please use properly formatted text for this purpose. – Giorgos Betsos Mar 24 '16 at 14:32
  • Are you using both MySQL and MS SQL Server here? Don't tag products not involved. – jarlh Mar 24 '16 at 14:45
  • 1
    Possible duplicate of [Select statement to find duplicates on certain fields](http://stackoverflow.com/questions/4434118/select-statement-to-find-duplicates-on-certain-fields) – Tab Alleman Mar 24 '16 at 14:49

2 Answers2

0

You can try to use the count(*) function to determine how many duplicates are there in the table.

select count(), colomn_filename, colomn_drawingnumber from table_name group by colomn_filename, colomn_drawingnumber having count()>1

Photops
  • 312
  • 2
  • 6
  • 20
0
SELECT fileName, drawingNo, count(*) FROM TableName 
GROUP BY fileName, drawingNo

Result:

Label 1 DWG#1 3

Label 1 DWG#2 2

If you really need the result same as yours then use left join

SELECT a.fileName, a.drawingNo FROM TableName a
LEFT JOIN (
SELECT fileName, drawingNo FROM TableName 
GROUP BY fileName, drawingNo 
HAVING COUNT(*) > 1
) b ON a.fileName = b.fileName AND a.drawingNo = b.drawingNo
KaiEn Suizai
  • 69
  • 1
  • 12