1

I have this general idea to find duplicate values taken from this post: Select statement to find duplicates on certain fields

select field1,field2,field3, count(*)
  from table_name
  group by field1,field2,field3
  having count(*) > 1

this works great to find the duplicates, but i need to also pull out a unique number, in this case an "order number" column that goes along with each row returned. This unique value cannot be used in the method above, because that would then return no rows as none would be exact duplicates. I need to be able to return this data but also find the records that occur multiple times in a table. I think this can be done with a union or using exists, but not sure how that would be accomplished. Any ideas?

sample result idea:

order number, field1, field2, field3
123             a       b        c
456             d       e        f
789             a       b        c

would want it to return order numbers 123 and 789 like this:

order number, field1, field2, field3
123             a       b        c
789             a       b        c
Community
  • 1
  • 1
wondergoat77
  • 1,765
  • 9
  • 32
  • 60

2 Answers2

3
;with a as
(
select count(*) over (partition by field1,field2,field3) count, order_number, field1,field2,field3
from table_name
)
select order_number, field1,field2,field3 from a where count > 1
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
1

I'm not entirely sure if this is what you want, but it sounds like maybe?

select min(t2.order_no), t2.field1, t2.field2, t2.field3, t1.cnt
from table_name t2, (
    select field1,field2,field3, count(*)
      from table_name
      group by field1,field2,field3
      having count(*) > 1
      ) t1
where t1.field1 = t2.field1      
and t1.field2 = t2.field2
and t1.field3 = t2.field3
group by t2.field1, t2.field2, t2.field3, t1.cnt

For each record returned in your deduplicating subquery, the outer query will attach to that record the smallest "order number" that matches the given combination of fields. If this isn't what you're looking for, please clarify. Some sample data and sample output would be helpful.

EDIT: From your posted sample data, it looks like you're looking to just return records that have dulpicates. If that's what you're looking for, try this:

select * 
from  table_name t2
where exists (
    select field1,field2,field3, count(*)
      from table_name t1
      where t1.field1 = t2.field1      
      and t1.field2 = t2.field2
      and t1.field3 = t2.field3 
      group by field1,field2,field3
      having count(*) > 1
      )

SQLFiddle

David Marx
  • 8,172
  • 3
  • 45
  • 66
  • i see what you were going for now, not exactly the answer i was looking for but i wasnt as clear as i probably should have been, thank you for the respsonse – wondergoat77 Jul 19 '13 at 15:42
  • There's no "count" in the sample result you posted. If you're just looking for a query that returns rows that have duplicates, check my updated answer. – David Marx Jul 19 '13 at 15:46
  • @PaulWIlliams reference the second query. – David Marx Jul 19 '13 at 15:48
  • both of your solutions are invalid – t-clausen.dk Jul 19 '13 at 16:01
  • the last t1 will cause an error. You can't name an item in the where clause – t-clausen.dk Jul 19 '13 at 16:04
  • @t-clausen.dk ah, thanks. That was an artifact from copy-pasting. removed. Tested via sqlfiddle. Thanks! – David Marx Jul 19 '13 at 16:08
  • if you remove ,count() from first part it will also work. Count() is invalid because it becomes a column without a name – t-clausen.dk Jul 19 '13 at 16:09
  • @t-clausen.dk I actually had that as `select 1 from ...` in an earlier iteration of my solution, but I decided to leave that in for clarity so OP could see the relationship between the subquery and the query they provided us with. – David Marx Jul 19 '13 at 16:10