Menu
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.
788
To select duplicate values, you need to create groups of rows with the same values and then select the groups with counts greater than one. You can achieve that by using GROUP BY and a HAVING clause.
If you want to identify duplicates across the entire data set, then select the entire set. Navigate to the Home tab and select the Conditional Formatting button. In the Conditional Formatting menu, select Highlight Cells Rules. In the menu that pops up, select Duplicate Values.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
