Geek Answers Handbook

SQL server finds duplicate in one column, but chooses where

I am trying to select rows from a table with duplicates in one column, but also restrict the rows based on another column. It seems to be working incorrectly.

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

If I delete where it works fine, but I need to limit it to only these lines.

 ID Terms Track Active 100 paper YY 200 paper YY 100 juice YY 400 orange NN 1000 apple YN

therefore, ideally, the query should return the first 2 rows.

+6
source share
3 answers
 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 )

SQLFiddle Demo

+6
source

Do not exactly get what you are doing. You use count(Terms) in having , however Terms is in your select clause. This means that for each entry, count(Terms) will be 1. Probably you should exclude Terms from select . Honestly, I reproduced your table and query, and it does not work.

Maybe this one is what you are looking for (?):

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

This will return all duplicate terms matching 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 need all the details of these terms, you can join 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

+1
source

More articles:


All Articles