was hoping someone could advise on the following problem.

I have a table similar to below and am trying to filter out all IDs that have the same subject and date (not just dedupe but remove complete) from my output.

So in the example above we would want to remove both rows with subject, "Welcome>"

I am able to write a query that shows me the count of IDs per subject and date, but stuck finding a way to remove these from my final output.

  select distinct 
  activitydate,
  name,
  subject,
  count (id)
  from activity
  where activity.activitydate between X and Y'
  and type IN ('Email')
  group by 1,2,3
  having count (id) > 30
  order by count (id) desc

Please let me know if any further details are needed and thank you in advance!

CodePudding user response：

-- The following intends to filter out rows that are duplicated
SELECT
  ID,
  Date,
  Subject,
  Type,
  COUNT(*)
FROM
  activity
GROUP BY
  ID,
  Date,
  Subject,
  Type
HAVING
  COUNT(*) < 2

CodePudding user response：

If you want to remove the rows where the COUNT by ID and SUBJECT is > 1 then you can do some thing like this.

-- untested

SELECT * FROM activity 
WHERE (ID,SUBJECT) NOT IN 
(SELECT ID,SUBJECT FROM activity 
GROUP BY ID, SUBJECT HAVING COUNT(*) > 1)