Hi Oracle Gurus,
Good Morning/Afternoon/Evening!!
There are quite a few methods to identify duplicate records efficiently. e.g. row_number() and group by however all these methods highlight the duplicate record only. Which means if your table has data like
| ID | Name | Room | Date |
| 1 | ABC | 203 | 7/20/2015 |
| 2 | FGH | 109 | 9/20/2015 |
| 3 | HSF | 202 | 8/20/2015 |
| 4 | REF | 201 | 8/20/2015 |
| 5 | FGH | 109 | 9/20/2015 |
| 6 | HSF | 291 | 8/24/2015 |
And I want to find duplicates on basis of Name/Room/Date
Most queries will give me
Either
| ID | Name | Room | Date |
| 5 | FGH | 109 | 9/20/2015 |
or
| ID | Name | Room | Date |
| 2 | FGH | 109 | 9/20/2015 |
They won't give me both the records unless I first do a group by (or Row_Number) in an inner query and then try to get both the rows in the outer query. I think that shouldn't be the way.
I need a report which brings out both the records only
| ID | Name | Room | Date |
| 2 | FGH | 109 | 9/20/2015 |
| 5 | FGH | 109 | 9/20/2015 |
Hope the question is clear.
Thanks in advance!!