I've been a Teradata user on and off for a few years now but I'm a bit stuck with a specific query. Basically, the table (tens of millions of records) I am trying to query contains the following key fields:
Basically, there are many instances of duplicate persons (i.e. the same name, DOB and post code) with multiple User IDs; so the unique individual has multiple records in the database, but every record itself is unique. I can run a simple 'group by' query to pull the counts of all combinations of duplicate first names, last names, DOBs and Post Codes. What I now need is to have the full list of User IDs for every instance of a duplicate person. What would be the best way of going about doing this? I bet there is a really straight-forward way but somehow I'm not seeing it at the moment...
I hope the request is clear. I am happy to clarify if needed. I'd really appreciate any help with this.
Based on your explanation this might be what you need:
over (partition by First_Name,Last_Name,Birth_Date,Post_Code) as minID
over (partition by First_Name,Last_Name,Birth_Date,Post_Code) > 1
It will return all rows for a group of duplicates with the same minID.
You can aslo try below
GROUP BY 1,2,3,4
HAVING COUNT(*) > 1