0

I have a DE which has a number of rows of data (with a number of columns)

ID Name date email
1 ABC 1234 @gmail

1 ABC 5678 @gmail

2 DEF 9101 @yahoo

3 GHI 1112 @yahoo

I want to run a query where I select all the rows where the ID value is unique

So the output would be

ID Name date email
2 DEF 9101 @yahoo

3 GHI 1112 @yahoo

I do not want the ID = 1 value as the value is not unique - it appears twice, and both the Group By and Distinct function will return a value and only removes the duplicates

ID Name date email
1 ABC 1234 @gmail

2 DEF 9101 @yahoo

3 GHI 1112 @yahoo

1 Answer 1

1

You were mostly there with the GROUP BY. You would just need to add another query above that to pull only those with a count of 1.

See below sample:

Select b.ID
FROM (
  SELECT a.ID,
  COUNT(a.ID) as cnt
  FROM [myDE] a
  GROUP BY a.ID
) b
WHERE b.cnt = 1

This will only return those that have a single entry for ID.


EDIT FROM COMMENT:

For more fields, you can use the aggregate function MAX() on the other fields to remove the need to include them in the GROUP BY statement. Since there is only 1 entry for these records, there is no worry about mixed records.

something like:

Select b.ID, b.Name, b.date, b.email
FROM (
  SELECT a.ID, 
  MAX(a.Name) as Name,
  MAX(a.date) as date,
  MAX(a.email) as email,
  COUNT(a.ID) as cnt
  FROM [myDE] a
  GROUP BY a.ID
) b
WHERE b.cnt = 1
3
  • Thanks for this Gortonington - is it possible to select additional columns at the same time ie Select b.ID, b.Name, b.Date, b.Email - The reason I ask is that when I try to grab additional columns from my DE I get the error - Invalid column name 'column_name'
    – Ap12345
    Commented Dec 11, 2020 at 21:41
  • @Ap12345 - added detail on getting the other columns inside of my answer Commented Dec 11, 2020 at 22:06
  • Thanks mate, I got it all worked out now, thanks for the help
    – Ap12345
    Commented Dec 14, 2020 at 15:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .