Finding duplicates on one column using select where in SQL Server 2008

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.

I think some sample data would help illustrate your issue... I notice you're grouping by Id, is this column not unique in this table? — Michael Fredrickson
– Michael Fredrickson, Commented Dec 17, 2012 at 19:29
there are no indexes on this table and will provide sample above — vbNewbie
– vbNewbie, Commented Dec 17, 2012 at 19:33

Stevoisiak · Accepted Answer · 2020-03-03 17:21:11Z

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

nidu · Accepted Answer · 2012-12-17 19:57:03Z

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

Michael Fredrickson · Accepted Answer · 2012-12-17 19:57:59Z

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

Collectives™ on Stack Overflow

Finding duplicates on one column using select where in SQL Server 2008

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related