Find duplicate combinations in Database

Question

Hi Everyone, 
 Is there a way find out duplicate combinations in Appian DB Table and delete them. 
 For Example there is a table with columns Name and Place

NAME 
 PLACE

John 
 Newyork

Tom 
 Washington

John 
 Newyork

Robert 
 Boston

Now how to find out the duplicate combination for John and Washington and keep only one record of that combination 
 
 Thanks

Chris · Answer

First, I never recommend deleting any data unless there are legal obligations to do so - design patters should be configured to utilize something such as a "cancelled" flag which allows the data to retain but removes them from reporting/process data. 
 However for related endeavors, I will typically create a view on the DB side which exposes a 'total count' column for the fields in question, if this is a specific activity. Alternatively, you will want to review a!queryAggregation() . This can help to return a list of distinct values across those fields, then you can query back to the DB over each set to determine the count of that combination and return the ids to use one for removal. 
 What is the data set size like? Manageable, or massive amounts of rows, etc? 
 a!localVariables(
 local!aggregationFields: {"requestByDivision","approval"},
 local!data: a!queryEntity(
 entity: cons!COE_DS_SAMPLE,
 query: a!query(
 paginginfo: a!pagingInfo(1,100),
 aggregation: a!queryAggregation(
 aggregationColumns: a!forEach(
 items: local!aggregationFields,
 expression: a!queryAggregationColumn(
 field: fv!item,
 isgrouping: true
 )
 )
 )
 )
 ),
 
 local!data.data
)

TJ · Answer

Hi, 
 You can also use the following database query to identify duplicate values. 
 
 select name, place, count(name) as count From dbTable Group By name, place Order by count(name) desc