Finding duplicates on one column using select wher

Question

I am trying to select rows from a table that have duplicates in one column but also restrict the rows based on another column. It does not seem to be working correctly.

select Id,Terms from QueryData 
where Track = 'Y' and Active = 'Y'
group by Id,Terms
having count(Terms) > 1

If I remove the where it works fine but I need to restrict it to these rows only.

ID      Terms     Track    Active
100     paper     Y        Y
200     paper     Y        Y
100     juice     Y        Y
400     orange    N        N
1000    apple     Y        N

Ideally the query should return the first 2 rows.

Answer 1

Don't exactly get what you're doing. You use count(Terms) in having however Terms is in your select clause. It means that for each records count(Terms) will be 1. Probably you have to exclude Terms from select list. Honestly i reproduced your table and query and it doesn't work.

Probably this is what you're looking for(?):

select Id, count(Terms) from QueryData 
where Track = 'Y' and Active = 'Y'
group by Id
having count(Terms) > 1

Answer 2

This will return all duplicated terms meeting the criteria:

select Terms
from QueryData
where Track = 'Y' and Active = 'Y'
group by Terms
having count(*) > 1

http://sqlfiddle.com/#!3/18a57/2

If you want all the details for these terms, you can join to this result.

;with dups as (
  select Terms
  from QueryData
  where Track = 'Y' and Active = 'Y'
  group by Terms
  having count(*) > 1
)
select
  qd.ID, qd.Terms, qd.Track, qd.Active
from 
  QueryData qd join
  dups d on qd.terms = d.terms

http://sqlfiddle.com/#!3/18a57/5

Answer 3

SELECT Id, Terms, Track, Active
FROM QueryData
WHERE Terms IN (
                SELECT Terms 
                FROM QueryData
                WHERE Track = 'Y' and Active = 'Y' 
                GROUP BY Terms
                HAVING COUNT(*) > 1
                )

Demo on SQLFiddle

Data:

ID      Terms     Track    Active
100     paper     Y        Y
200     paper     Y        Y
100     juice     Y        Y
400     orange    N        N
1000    apple     Y        N

Results:

Id      Terms     Track    Active
100     paper     Y        Y
200     paper     Y        Y