How to show records were 3 fields match

Question

I'm trying to write a query that will list the columns in a table when 3 specific fields are the same, but unknown:

TABLE:

FIELD 1  |   FIELD 2    |  FIELD 3   |   FIELD 4
---------|--------------|------------|---------------
  1      |   01-01-15   |    21      |     150
  1      |   01-01-15   |    24      |      12
  1      |   02-01-15   |    21      |     681
  1      |   01-01-15   |    21      |     299

DESIRED RESULTS:

    FIELD 1  |   FIELD 2    |  FIELD 3    |   FIELD 4
-------------|--------------|-------------|------------
      1      |   01-01-15   |    21       |     150
      1      |   01-01-15   |    21       |     299

Sorry - still a newb here! Thanks in advance!

Why return 2 records and not 3? – juergen d Jun 03 '15 at 20:18 — juergen d, Jun 03 '15 at 20:18

score 3 · Accepted Answer · answered Jun 03 '15 at 20:19

3

Count the number of rows with the same combination and filter for a count > 1:

select *
from tab
qualify 
   count(*) 
   over (partition by field1, field2, field3) > 1

answered Jun 03 '15 at 20:19

dnoeth

59,503
4
39
56

Perfect dnoeth! I haven't used the qualify statement yet. Thank You! – DJDJ23 Jun 03 '15 at 20:25

How to show records were 3 fields match

1 Answers1